The Notorious Date Formatting Bug
One of the most universally despised features in Microsoft Excel is its aggressive data conversion engine. If you type a fraction (like 1/4) or a specific identifier code (like MAR20), Excel assumes you are trying to type a date and instantly converts your text into “01-Apr” or “Mar-20”. For decades, scientists inputting gene names (like “MARCH1”) and accountants entering part numbers have been forced to fight this automation by manually adding apostrophes before every entry. Fortunately, modern versions of Excel finally include a setting to disable this behaviour.
How to Disable Automatic Data Conversions
If you are using a recent version of Microsoft 365, you can turn off the automatic date conversion feature entirely through the main options menu.
1. Open Microsoft Excel.
2. Click on the File tab in the top-left corner.
3. Select Options at the bottom of the left-hand menu.
4. In the Excel Options window, click on Data in the left sidebar.
5. Look for the section titled “Automatic Data Conversion.”
6. Locate the checkbox labelled Convert continuous letters and numbers to a date.
7. Untick this checkbox.
8. Click OK to save your changes.
Handling Older Versions of Excel
If you are using an older version of Excel (like Excel 2019 or older) that does not have the “Automatic Data Conversion” menu, you cannot disable the feature system-wide. Instead, you must use a formatting workaround. Before typing your numbers, highlight the empty cells you plan to use, go to the Home tab, click the Number Format dropdown (which usually says “General”), and select Text. Because the cells are explicitly formatted as text, Excel will finally stop trying to guess if your numbers are dates.