Dates change when you open a CSV because the file stores everything as plain text, and Excel guesses what each value is. Anything that looks a bit like a date becomes one: 1-2 turns into 2 January, the gene name MARCH1 into 1 March, and 03/04/2025 into either 3 April or 4 March depending on your computer’s region settings. The fix is to stop Excel guessing, or to convert the file with something that reads the whole column before deciding.
Three ways dates go wrong
1. Text that isn’t a date becomes one
Excel converts values such as 1-2, 3/4, JAN1 or SEPT1 into dates. For part numbers, survey codes or scores, that’s corruption, and saving the file makes it permanent.
The best-known case is genetics. A 2016 study in Genome Biology looked at the spreadsheets supplied with 3,587 papers in 18 journals and found gene names converted to dates in 19.6% of them (Pesquisa FAPESP). In 2020 the committee that names human genes gave up and renamed some: SEPT1 became SEPTIN1 and MARCH1 became MARCHF1, so Excel would leave them alone.
2. Day and month swap
03/04/2025 means 3 April in the UK and March 4 in the US. Excel reads it using your computer’s region setting, so the same file gives different dates on different computers. Worse, it’s inconsistent within a file: on a UK-set computer, a US-format 03/25/2025 can’t be a valid day-first date, so it stays as text while 03/04/2025 beside it becomes 3 April. You end up with a column that’s half dates and half text, with the dates silently wrong.
3. Dates become numbers
Excel stores dates as a count of days since 1900. When a date column ends up formatted as a number, or a date is copied to another program, you see values like 45748 instead of 1 April 2025.
Fix 1: switch off the guessing in Excel
In Excel for Microsoft 365 and Excel 2024 there’s a setting for exactly this. On Windows go to File › Options › Data; on a Mac go to Excel › Preferences › Edit. Under Automatic Data Conversion, untick Convert “date-like” combinations of continuous letters and numbers into a date.
Microsoft notes a limit: values with a space or punctuation, such as JAN 1 or JAN-1, may still become dates (Microsoft). For full control, use the next fix.
Fix 2: import the file and set the column types
Open a blank workbook, choose Data › From Text/CSV, then Transform Data. Set code columns to Text. For real dates, right-click the column header and choose Change Type › Using Locale, then pick the date format the file actually uses, such as English (United States) for month-first dates.
Fix 3: convert the file to Excel first
The JFDI file converter looks at the whole column before deciding what a date means. If any value in it is like 25/03/2025, which can only be day-first, it reads the whole column day-first. Only if a column is truly ambiguous does it fall back to your browser’s language, and you can set it yourself under Reading the file › Dates like 03/04/2025 mean.
Real dates become real Excel dates you can sort and calculate with. Codes that only look like dates stay as text, and a column that mixes dates with other text keeps those values as text so nothing is lost.
When it writes CSV files, dates come out as 2025-03-31 (the ISO 8601 format), which every program reads the same way.
How to write dates so this never happens
If you produce CSV files for other people:
- Write dates as YYYY-MM-DD.
2025-04-03can’t be misread, and it sorts correctly as text. - Keep codes unambiguous. A prefix or letters elsewhere (
PN-0102rather than1-2) avoids accidental dates. - Say what each column is in a header or a readme, especially for IDs.