Import CSV dates into FileMaker without swapping day and month

Ask what 04/05 means before importing it

The string 04/05/2026 can mean 4 May or April 5. No calculation can infer the intended date from that value alone. Get the source format from the provider, stage the original text, and convert only after the format is known.

Changing the destination field’s display format will not repair a date that was stored incorrectly. Keep the original string alongside the converted date so you can explain what happened. A blank source value also needs its own policy: allowed missing date or unresolved required date.

Use one explicit format

For this exercise, the provider promises exactly two day digits, a slash, two month digits, a slash and four year digits: DD/MM/YYYY. Whitespace, shorter forms and timestamps are rejected. Restrict years to 1900–2100 for this fictional business; change that range deliberately for another domain.

Validate the shape and the resulting components

Use this calculation for a proposed date result. Incoming::RawDate is a text field. The result should be configured as Date if you use a calculation field.

Let ( [
    raw = Incoming::RawDate ;
    digits = Left ( raw ; 2 ) & Middle ( raw ; 4 ; 2 ) & Right ( raw ; 4 ) ;
    shapeOK = Length ( raw ) = 10
        and Middle ( raw ; 3 ; 1 ) = "/"
        and Middle ( raw ; 6 ; 1 ) = "/"
        and Length ( digits ) = 8
        and Exact ( Filter ( digits ; "0123456789" ) ; digits ) ;
    d = GetAsNumber ( Left ( raw ; 2 ) ) ;
    m = GetAsNumber ( Middle ( raw ; 4 ; 2 ) ) ;
    y = GetAsNumber ( Right ( raw ; 4 ) ) ;
    rangeOK = d ≥ 1 and d ≤ 31 and m ≥ 1 and m ≤ 12
        and y ≥ 1900 and y ≤ 2100 ;
    candidate = If ( shapeOK and rangeOK ; Date ( m ; d ; y ) ; "" )
] ;
    If ( shapeOK and rangeOK
        and Day ( candidate ) = d
        and Month ( candidate ) = m
        and Year ( candidate ) = y ;
        candidate ; ""
    )
)

Date always takes month, day, year in that order. It can normalize out-of-range components into another date, so the final component comparison matters. See Claris’s Date reference. Do not accept a nonempty candidate without those checks.

Keep missing and invalid separate

An empty proposed date is not enough information for a report. Classify raw blank as Missing, a nonblank rejected value as Invalid for DD/MM/YYYY, and a valid result as Ready. If a date is optional, Missing may be acceptable, but it must not be counted as a successfully parsed date.

For MM/DD/YYYY, create a separate named parser with different component positions. Do not switch parsers based on which one happens to return a date. A value where both interpretations are valid would still be ambiguous.

Before writing, show the original text and an unambiguous preview such as 4 May 2026. Commit only approved values and capture validation errors; leave the source row available for correction.

Test the awkward dates

Run every row in the table, then add 00/05/2026, 15/13/2026 and a trailing space. All three should be rejected under this strict contract. Confirm that 04/05/2026 remains 4 May after saving, closing and reopening the record.

If the provider’s format is still unknown, stop at staging. Reporting an unresolved date is more useful than silently storing a plausible but incorrect one.