CSV Dates Have Day and Month Swapped: Import with the Right Locale

Establish what the source dates mean

Ask the exporter or inspect its field specification before converting anything. An unambiguous value such as 13/05/2026 is evidence for day-first dates in that row, but it does not prove that files from several countries share one convention. Keep the original CSV and record the agreed pattern beside the imported table.

Our example is explicitly day-first. A101 therefore means 4 May, even though a month-first reader could accept it as 5 April without reporting an error. Those silently accepted rows need as much attention as rows that fail to import.

Import the untouched text before converting

Start from the file, not a workbook that has already guessed the dates. In Excel desktop, open Data > Get Data > From File > From Text/CSV and choose Transform Data. Ribbon wording varies by version. The important point is reaching the query editor before accepting a guessed date column.

Inspect Applied Steps. If an automatic Changed Type step has already interpreted ordered_on, remove that step or edit it so the date column stays Text before your deliberate conversion. Preserve other useful transformations. Add a duplicate of the original date-text column if you need a side-by-side audit.

1. Select ordered_on in the query editor and verify the values still match the CSV text.
2. Right-click its heading and choose Change Type > Using Locale.
3. Choose Date and English (United Kingdom) for this DD/MM/YYYY example.
4. Check all three sample rows and any conversion errors, then Close & Load.

Verify the day and month separately

Display the loaded date with a four-digit year and an unambiguous order such as yyyy-mm-dd. Then compare the calendar meaning with the original column. Formatting is useful for inspection after conversion; it is not a replacement for selecting the correct source convention.

A helpful spot check includes a day above 12, a date where day and month differ, a month boundary, and any leap-day records. For the example, inspect A101 and A103 as well as A102. A conversion that only succeeds on 13/05/2026 has not demonstrated that the ambiguous values are right.

order_id,ordered_on
A101,2026-05-04
A102,2026-05-13
A103,2026-06-05

Keep formatting separate from interpretation

If A101 has already become 5 April, applying dd/mm/yyyy will show 05/04/2026. Excel is displaying the same underlying date differently. Reimporting the original source text with the correct locale is the dependable repair when the earlier conversion was wrong.

For future exports, request a documented year-month-day representation with four-digit years. It makes human review easier and avoids the common slash-date ambiguity. Continue to specify an import type: a CSV carries characters, not the workbook's cell types or display settings.

Related help: Convert text dates before sorting the table

Handle mixed dates without guessing

A column containing both 04/05/2026 and 2026-05-04 needs an explicit rule for each documented pattern. Add a source or format field, convert each group separately, and combine the validated results. Do not infer the pattern independently for every ambiguous row.

Keep missing dates missing. Flag impossible dates and incomplete years for review instead of filling in a convenient default. If the export includes times or time-zone offsets, use a separate date-time conversion plan so that a date-only cleanup does not discard information. The Column Harbor checker can flag selected risks in UTF-8 CSV text; it cannot establish the intended locale or repair an already converted workbook.