A plausible date can still be the wrong date
03/04/2026 can mean April 3 or March 4. The characters alone cannot tell you which meaning the source intended. Check the exporting system, its settings, and an unambiguous reference record before choosing a conversion rule. A clean-looking ISO date is useful only if the interpretation behind it is correct.
Work with the example
Mixed-date practice CSV ↓All records are fictional. Import CSV identifier columns as Text. Quoted CSV fields do not define spreadsheet column types.
Open the mixed-date review tool, load the sample, leave the rule at “Review ambiguity,” and run it. Dates already in YYYY-MM-DD are checked as calendar dates.
Read the sample outcomes
| Original value | Review-ambiguity result | Reason |
|---|---|---|
| 03/04/2026 | Ambiguous | Both first components are valid month numbers. |
| 28/10/2026 | 2026-10-28 | Only the day-first interpretation is valid. |
| 10/28/2026 | 2026-10-28 | Only the month-first interpretation is valid. |
| 2026-11-01 | 2026-11-01 | ISO-format input. |
| 31/02/2026 | Invalid | February has no 31st day. |
| Blank | Blank | No date was supplied. |
| 29/02/2024 | 2024-02-29 | 2024 is a leap year. |
The first run produces four converted values, one ambiguous value, one invalid value, and one blank. The example deliberately mixes conventions; it is not evidence that a real export mixes them.
Apply one explicit source rule
Select day/month/year if you have verified a day-first source. The ambiguous first row becomes 2026-04-03, while 10/28/2026 becomes invalid under that rule. The tool does not silently switch conventions for that row. With month/day/year selected, the first row becomes 2026-03-04 and the day-first rows become invalid.
For a genuinely mixed file, separate records using reliable source metadata and apply a documented rule to each group. Do not infer a country from a customer's name or phone number. If there is no reliable evidence, keep the date unresolved.
Carry the rule into the spreadsheet
In Power Query, review automatic type detection and use a locale that matches the source when converting a text column to Date. Locale affects interpretation; changing only the display format afterwards does not reverse an incorrect interpretation. Keep the original text alongside the converted date while reviewing exceptions.
The tool's download contains the original date, status, proposed ISO date, and explanation. It is an audit trail rather than an automatically cleaned source file. It supports numeric slash or hyphen dates with four-digit years and ISO dates from 1900 to 9999. It does not parse timestamps, month names, spreadsheet serial dates, or timezone offsets.
Sources & further reading
The sample data and worked calculations are original instructional examples. Spreadsheet menus and supported features depend on your installed version. See the verification method for the checks performed.
Continue the workflow