{"id":1016,"date":"2026-10-23T17:31:50","date_gmt":"2026-10-23T17:31:50","guid":{"rendered":"http:\/\/jfdi-blog-csv-dates-changing-excel"},"modified":"2026-10-24T13:50:16","modified_gmt":"2026-10-24T13:50:16","slug":"csv-dates-changing-excel","status":"publish","type":"post","link":"https:\/\/jfdi.pro\/blog\/csv-dates-changing-excel\/","title":{"rendered":"Why dates change when you open a CSV, and how to stop it"},"content":{"rendered":"\n

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<\/code> turns into 2 January, the gene name MARCH1<\/code> into 1 March, and 03\/04\/2025<\/code> 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.<\/p>\n\n\n\n

\n
Convert CSV to Excel, dates handled properly<\/a><\/div>\n<\/div>\n\n\n\n

Three ways dates go wrong<\/h2>\n\n\n\n

1. Text that isn’t a date becomes one<\/h3>\n\n\n\n

Excel converts values such as 1-2<\/code>, 3\/4<\/code>, JAN1<\/code> or SEPT1<\/code> into dates. For part numbers, survey codes or scores, that’s corruption, and saving the file makes it permanent.<\/p>\n\n\n\n

The best-known case is genetics. A 2016 study in Genome Biology<\/em> looked at the spreadsheets supplied with 3,587 papers in 18 journals and found gene names converted to dates in 19.6%<\/strong> of them (Pesquisa FAPESP<\/a>). In 2020 the committee that names human genes gave up and renamed some: SEPT1<\/code> became SEPTIN1<\/code> and MARCH1<\/code> became MARCHF1<\/code>, so Excel would leave them alone.<\/p>\n\n\n\n

2. Day and month swap<\/h3>\n\n\n\n

03\/04\/2025<\/code> 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<\/code> can’t be a valid day-first date, so it stays as text while 03\/04\/2025<\/code> beside it becomes 3 April. You end up with a column that’s half dates and half text, with the dates silently wrong.<\/p>\n\n\n\n

3. Dates become numbers<\/h3>\n\n\n\n

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<\/code> instead of 1 April 2025<\/code>.<\/p>\n\n\n\n

Fix 1: switch off the guessing in Excel<\/h2>\n\n\n\n

In Excel for Microsoft 365 and Excel 2024 there’s a setting for exactly this. On Windows go to File \u203a Options \u203a Data<\/strong>; on a Mac go to Excel \u203a Preferences \u203a Edit<\/strong>. Under Automatic Data Conversion<\/strong>, untick Convert “date-like” combinations of continuous letters and numbers into a date<\/strong>.<\/p>\n\n\n\n

Microsoft notes a limit: values with a space or punctuation, such as JAN 1<\/code> or JAN-1<\/code>, may still become dates (Microsoft<\/a>). For full control, use the next fix.<\/p>\n\n\n\n

Fix 2: import the file and set the column types<\/h2>\n\n\n\n

Open a blank workbook, choose Data \u203a From Text\/CSV<\/strong>, then Transform Data<\/strong>. Set code columns to Text<\/strong>. For real dates, right-click the column header and choose Change Type \u203a Using Locale<\/strong>, then pick the date format the file actually uses, such as English (United States)<\/em> for month-first dates.<\/p>\n\n\n\n

Fix 3: convert the file to Excel first<\/h2>\n\n\n\n

The JFDI file converter<\/a> looks at the whole column before deciding what a date means. If any value in it is like 25\/03\/2025<\/code>, 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 \u203a Dates like 03\/04\/2025 mean<\/strong>.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

When it writes CSV files, dates come out as 2025-03-31<\/code> (the ISO 8601 format), which every program reads the same way.<\/p>\n\n\n\n

How to write dates so this never happens<\/h2>\n\n\n\n

If you produce CSV files for other people:<\/p>\n\n\n\n