Why Excel converts text to dates (and how to stop it)
Excel converts values that look like dates when it opens a CSV. 1-2 becomes 2-Jan, Mar-1 becomes a date, and gene names such as SEPT1 and MARCH1 turn into dates too.
What gets converted
- Short hyphen or slash forms:
1-2,3/4 - Month and number combinations:
Mar-1,1-Mar,DEC1 - Full numeric dates:
01/02/03,12.05.24
Excel stores the result as a date serial number, so the original text is lost.
Ambiguous day and month order
01/02/03 could be 1 February 2003, 2 January 2003 or 3 February 2001. Excel decides using your computer’s regional settings, so the same file can open differently on two machines.
The gene-name problem
A 2016 study found gene symbols converted to dates in roughly one in five published papers with supplementary gene lists. In 2020 the gene naming committee renamed genes such as SEPT1 and MARCH1 to avoid the problem. Other identifiers can suffer the same fate.
How to prevent it
- Import with Data > From Text/CSV and choose “Do not detect data types”.
- Set the affected columns to Text before loading.
- If you want real dates, store them as
yyyy-mm-ddso the order is unambiguous. - Use KeepLeadingZeros.com to flag date-like tokens and export them as text.