🧹 Data Cleaning · 8 min read

Why CSV Dates Keep Changing When You Save

The date you typed is not the date that comes back. A file goes out as 03/05/2026, arrives as March 5th, and the person who sent it swears it left as the third of May. Nothing corrupted, and nothing was lost. The file never held the fact that was being argued about.

This article covers where the guess happens, what a wrong guess actually costs, and the two changes that stop it from mattering again.

Quick answer

A CSV stores a date as text, with no record of whether the day or the month came first. The program that opens the file supplies that decision from the machine's regional settings, so the same bytes turn into different dates on different computers. The fix is to write dates in a form that cannot be misread, 2026-09-23, and to import through a step where you set the column type yourself instead of letting the automatic conversion decide.

CSV has no date type

RFC 4180 describes a file of records, each record on its own line, each field separated by a comma. There is no column type, no length and no format recorded anywhere in the file. A phone number, an account code and a date all arrive the same way: as characters.

That design is why CSV opens in everything, and it is also why dates are the field most likely to shift on the way through. The file is not wrong. It simply did not carry the information needed to be right on its own.

Where the guess happens

Microsoft's support page on importing and exporting text and CSV files describes the moment of conversion, and it is worth reading the sentence twice:

When Excel opens a .csv file, it uses the current default data format settings to interpret how to import each column of data. If you want more flexibility in converting columns to different data formats, you can use the Import Text Wizard.

The same page gives the exact example that matches this problem: the format of a data column in the .csv file may be MDY, but Excel's default data format is YMD. Two different conventions, one file, and an automatic decision in between.

The date you typed is not what sits in the cell either. Microsoft's documentation on date systems says that when you enter a date it becomes a serial number counting days since January 1, 1900, and gives July 5, 2011 as the number 40729. What you see on screen is a number with a display format on top. A CSV has nowhere to put the format, so only the text survives, and the machine that opens it formats the text again its own way.

What a wrong guess costs, measured

We built a file written day-first, the way a UK, German or Australian system writes dates, with 10,000 rows of dd/mm/yyyy text. Then we read it back the way a US-default program would, as mm/dd/yyyy, and compared every row against the date it was supposed to be.

Of the rows that parsed cleanly, 3,334 out of 4,450 came back as the wrong date, which is 74.9% of everything the reader accepted. Those are the dangerous ones. A failed parse shows up as an empty cell or an error and somebody notices. A swap between the 4th of March and the 5th of April looks like a plausible date, sorts correctly within the month, and reconciles against nothing.

Three rows make the mechanism visible. All three were written from the same day-first source, and all three were read back as month-first:

The third row is the reason this problem survives for years. Every month where the day and month are both 12 or lower has a share of rows that come back correct no matter which convention the reader chose, so a spot check of ten rows can look clean while a third of the file is shifted.

The setting lives on the machine

Excel does not read a date format from the file, because there is none to read. It uses the operating system's regional settings, which is why the same CSV can be correct on one laptop and wrong on the laptop next to it, and why a report can be right for four months and then wrong after somebody travels with the company laptop. Microsoft notes that date formats beginning with an asterisk in the format list are the ones that change when regional date and time settings change.

If two people disagree about what a file says, this is usually why. It is worth checking the regional setting before checking the rows.

Four fixes that hold

1. Write ISO 8601 dates. 2026-09-23 cannot be misread, because the year comes first and there is only one way to assign the remaining parts. It also sorts correctly as text, which matters when a downstream tool sorts a column as a string.
2. Import through the wizard, not the double click. Opening a .csv by double-clicking applies the automatic conversion. Use Data → From Text/CSV and set the date column type explicitly. Microsoft also notes a shortcut worth knowing: changing the file extension from .csv to .txt forces Excel to run the Import Text Wizard.
3. Let the CSV be the exchange file, not the master copy. Keep types and formats in the workbook or database, where they are stored, and treat the CSV as the transfer format. A CSV is a good way to move data; it is a poor place to store meaning.
4. Record the convention next to the file. If you must ship day-first text, say so in the filename, the readme, or the column header, date_dd_mm_yyyy. It is a small thing that prevents the argument later.

Repairing a file that already lost the order

1. Look for any first number greater than 12 in the date column. If one exists, the file was written day-first, because no month is larger than 12. That makes the diagnosis cheap on a file with enough rows in it.
2. If everything sits at 12 or below, compare a few rows against an independent source, such as a monthly total or a known transaction date, before converting anything.
3. Convert every row with one rule, never row by row, and re-check the row count afterwards so nothing silently dropped out of the parse.
4. If the file also has everything in a single column or long numbers turned into 1.23E+15, the same reader is making more than one guess, and it is worth fixing all of them in the same pass.

How to check a file before you send it

Paste a slice into the CSV analyzer and read the columns as a parser sees them rather than as a spreadsheet guessed them. If a date column needs a second look, the analyzer shows the raw text, which is the only thing that is actually in the file. When the goal is simply to rewrite the file with a different separator or a different encoding, the delimiter converter does that without reinterpreting the values.

If you are dealing with a related problem, the leading zeros guide covers the same class of automatic conversion and the Excel to CSV walkthrough covers what an export does and does not preserve.

Frequently asked questions

The quoted sentences about interpreting each column, and about forcing the Import Text Wizard, are from Microsoft's support page "Import or export text (.txt or .csv) files", retrieved on 2026-09-23. The serial number description and the 40729 example are from Microsoft's date systems documentation, retrieved the same day. The parse counts come from a 10,000-row test we ran on 2026-09-23 using datetime.strptimein Python's standard library. Field rules and the absence of type information are from RFC 4180, section 2.