Practical diagnosis

Resolve day/month ambiguity before conversion

Reviewed 8 October 2026 · Synthetic examples

The text 03/04/2026 has two ordinary interpretations: March 4 and April 3. Both are valid dates. A successful import therefore proves very little. Find the producer's date convention, a data dictionary, or an independently known event before choosing an interpretation.

Build a tiny evidence set Use three synthetic strings: 03/04/2026, 13/04/2026, and 04/13/2026. Under an explicitly day/month/year contract, the first means April 3 and the second April 13; the third is invalid. Under month/day/year, the first means March 4, the second is invalid, and the third means April 13. These are expected meanings under the two contracts, not promises about automatic app parsing.

A row whose day exceeds 12 is useful evidence, but does not prove that every row follows one convention. A merged export may contain several sources. Check the whole column for mixed conventions and preserve ambiguous rows for review instead of guessing from the first few entries.

Choose a provider-specific path In Excel Power Query, convert a text column using Change Type > Using Locale, selecting Date and the locale matching the source. Inspect earlier applied steps first: a previous automatic type conversion may already have interpreted the text. Recover the original text at the earlier step rather than applying another conversion to an incorrect date.

Google Sheets has spreadsheet-wide locale and time-zone settings under File > Settings. Its DATEVALUE documentation says recognized formats can depend on region and language. Changing the setting affects the shared spreadsheet, so use a separate working copy and check with collaborators before changing their file. Do not expect a locale change to reconstruct text that was already parsed incorrectly.

Remove ambiguity at the boundary When you already have validated numeric year, month, and day components, construct =DATE(2026,4,3) to mean April 3. Use four-digit years. English function names and comma separators are used in these examples; localized spreadsheet syntax can differ.

For a controlled exchange, document date-only text as YYYY-MM-DD, preserve that text at import, then convert deliberately. A recognizable layout is helpful to people; it is not a universal guarantee that every importer will recognize it correctly.

Finally, inspect separate year, month, and day components in the result against the expected record. For our day-first example, April 3 must yield year 2026, month 4, day 3. If it instead yields month 3 and day 4, stop. A prettier number format would conceal the mistake rather than fix it.