Excel changed your CSV dates. Here is why, and how to stop it.

You send a report to a colleague. Their copy shows March where yours shows April. Nobody edited anything. The file is identical on both machines, and the dates are different, because 03/04/2026 does not mean anything on its own.

Date handling is where a CSV's lack of types hurts the most, because unlike a truncated zip code, a wrong date usually looks completely normal.

Symptom: the day and month are swapped

Cause. The file contains a date written in a format that is genuinely ambiguous, and Excel resolves it using your operating system's short date setting. In the United States that is month first, so 03/04/2026 is the fourth of March. Nearly everywhere else it is day first, so the same characters mean the third of April.

The insidious detail is that only ambiguous dates flip. A file covering a full year is read correctly for the days above the twelfth, because 25/06/2026 has no valid month-first reading, and misread for everything from the first to the twelfth. So the column is not uniformly wrong. It is wrong in a pattern, which is much harder to spot in a chart and much harder to explain to whoever signs off the report.

Fix. Never rely on the ambiguous form. If you produce the file, write ISO. If you receive it, pin the format on import rather than letting the locale decide.

Fix it: load the file and set an explicit date format →

Fix: standardize on ISO 8601

2026-04-03 for a date. 2026-04-03T14:30:00Z for a timestamp, with the Z meaning UTC. Three properties make it the right answer and no other format has all three.

• Unambiguous. Year, month, day, largest unit first. There is no locale in which it means something else.
• Sortable as text. Alphabetical order is chronological order, which means it works even in a tool that never parses it as a date.
• Universally parsed. Excel, Sheets, DuckDB, Postgres, Python, JavaScript and every CSV library recognize it without configuration.

To convert an existing column, cast with an explicit format so nothing is left to inference:

SELECT STRFTIME(
         STRPTIME(order_date, '%d/%m/%Y'),
         '%Y-%m-%d'
       ) AS order_date
FROM data;

You have to know which way round the source is. If you are not certain, the next section tells you how to find out from the data itself.

Fix it: normalize the whole column to ISO in one step →

Fix: pin the format at import time

Every serious parser lets you state the format instead of guessing it. DuckDB, which runs under our workbench, takes dateformat and timestampformat options on its CSV reader, and our parse-options dialog passes them straight through. Set %d/%m/%Y and every row is read the same way regardless of the machine.

In Excel's Power Query, the Locale option in Change Type, Using Locale does the same job. Pick the locale the file was written in, not the one you are sitting in.

In pandas, pd.to_datetime(col, format="%d/%m/%Y") with an explicit format is both faster and safer than letting the parser infer per value, which it will happily do row by row and inconsistently.

Symptom: something that was never a date became one

Cause. Excel converts anything date-shaped, and its notion of date-shaped is broad. A few real examples that bite people:

The gene name case became genuinely notorious. Enough published genomics datasets were corrupted this way that in 2020 the naming committee renamed a set of human genes to stop spreadsheets mangling them. That is the level at which this bites: it was easier to rename the genes than to fix the workflow.

Fix. Import the column as text. Once a value has become a date, the original characters are gone and the cell holds a serial number. Reformatting to text shows you 44317, not MAR1.

Fix it: convert to a typed workbook where the column is already text →

Serial numbers, and the time zone trap

A date in Excel is a number: days since 30 December 1899, with time as a fraction of a day. Export to CSV and you may get 45001 instead of a date, depending on the cell format at the moment of export. To convert back:

SELECT DATE '1899-12-30' + CAST(serial AS INTEGER) AS real_date
FROM data;

Note the epoch is the thirtieth, not the thirty-first. That offset accounts for Excel's treatment of 1900 as a leap year, which it was not, kept for compatibility with Lotus 1-2-3 in the 1980s and never fixed.

Timestamps add time zones. A CSV timestamp with no offset is a claim without a context. If the exporting system was in UTC and the reading system is in Asia/Kolkata, a naive parse shifts every value by five and a half hours, and daily aggregates land in the wrong day for anything recorded after 18:30. Write the offset, or write UTC and say so.

Check what is really in the file

You can often work out the source format from the data alone, without asking anyone.

1. Look for a first component above 12. If any row has 25/06/2026, the file is day first. One such row settles it for the whole column.
2. Look for a second component above 12. If any row has 06/25/2026, it is month first.
3. If neither appears, ask. A file whose dates are all in the first twelve days of the month is genuinely undecidable. Do not guess; a wrong guess is invisible.
4. Check the distribution. Group by month and count. A real year of business data is roughly even across months. A misparsed day-first file read as month-first bunches everything into the first twelve days of each month, which shows up instantly in a chart.

That last check is my favorite because it needs no knowledge of the source system. Load the file, group by month, and look at the shape. Broken date parsing has a very distinctive silhouette.

Fix it: chart the date column and see whether the distribution makes sense →