A spreadsheet does not store a date — it stores a number counting days from a fixed origin, and displays it using a format. CSV has no formats, so what gets written is whatever the display happened to be, and whatever reads it next guesses. Exporting in ISO format (2026-03-09) removes the guessing.
The Orders sheet contains 9 March 2026 and 3 September 2026, plus ISO text equivalents. Export it with Excel to CSV, then inspect the raw CSV in a text viewer. Verify both dates and the leading zeros in order 00123 before reopening the file in a spreadsheet. CSV does not preserve cell types; import identifiers as text when required.
You export a sheet to CSV and the dates arrive wrong. Sometimes they are five-digit numbers. Sometimes the ninth of March has become the third of September. Sometimes they look right until someone in another country opens the file.
All three have the same root cause, and it is not a bug in the export.
What a date cell actually contains
A spreadsheet stores a date as a number: the count of days since an origin date. In Excel, 1 January 1900 is day 1, so 9 March 2026 is stored as roughly 46,090. The cell also carries a display format — "dd/mm/yyyy", say — and that format is the only reason you see a date rather than a number.
The number is the data. The format is a costume. CSV is a plain text file with no room for costumes, so exporting has to choose: write the number, or write what the costume currently looks like.
The three failure modes
Dates became five-digit numbers
The export wrote the underlying value rather than the formatted one. 46090 is not corruption — it is the date, unformatted. It is recoverable, but only if you know the origin.
Day and month swapped
The export wrote "03/09/2026" and whatever opened it read that as month-first rather than day-first. Both readings are valid; the file gives no way to tell them apart. This is the most damaging failure because it is silent — every date from the 1st to the 12th converts to a plausible wrong date, and only the 13th onward throws an error that reveals the problem.
Leading zeros disappeared
Not strictly a date problem, but it arrives with the same export. Postcodes, phone numbers and reference codes beginning with zero are read as numbers, and 00123 becomes 123. Once written that way, the zeros are gone.
The fix: export ISO dates
The ISO 8601 format — 2026-03-09, year first — is unambiguous. There is no locale in which it means something else, it sorts correctly as plain text, and every database, language and spreadsheet reads it the same way.
Choose the date format at export time rather than hoping the cell formatting survives. That single decision removes the whole class of problem.
In much of Europe the comma is the decimal separator, so spreadsheets there use a semicolon to separate CSV fields. A comma-delimited file opened in that locale lands entirely in one column, and a semicolon-delimited file opened elsewhere does the same.
If you know where the file is going, match the delimiter to the destination. If you do not, comma with quoted fields is the safer default — and worth saying explicitly when you send it.
Checking an export before you send it
Open the CSV in a plain text editor, not a spreadsheet. A spreadsheet re-guesses the formats and hides exactly what you are trying to inspect.
Look at a date. Is it 2026-03-09, or something a reader has to interpret?
Find a date after the 12th of a month. If it reads correctly and one before the 12th does too, the interpretation is consistent.
Check any column of codes for missing leading zeros.
Opening in a text editor is the important step. Most date problems are invisible in a spreadsheet, because the spreadsheet re-applies a format over whatever it guessed.
Fixing a file you already received
If dates arrived as numbers, they are recoverable — you need the origin the exporter used, which is almost always 1 January 1900 for Excel or 30 December 1899 for some other tools.
If day and month were swapped, they may not be recoverable at all. Where both values are 12 or lower the original is genuinely ambiguous, and only an outside reference — a row you know the true date of — can settle it. This is why getting the export right matters more than fixing it afterwards.
To see what a file actually contains before trusting it, data type detection infers each column’s type and lists the rows that do not match, which surfaces a handful of malformed dates in a large file quickly.
Use ISO dates in every file that leaves your machine. It looks less friendly than 09/03/2026 and it is the only format that cannot be misread. Formatting for humans belongs in the report; data interchange should be unambiguous.