Why Excel Turns Numbers into Dates and How to Stop It
Excel and Google Sheets love to 'guess' formats: paste an SKU like 03-04 and you get April 3. Here's why it happens and 5 working ways to permanently stop the auto-conversion.
Anyone who has worked in Excel or Google Sheets has hit this: you paste an SKU, a phone number, a release version, or an ISBN — and the spreadsheet silently turns it into a date. '03-04' becomes April 3, '1.2.10' becomes January 2010, '1/2' becomes January 1. The worst part is that after the auto-conversion the original value is often lost: Excel stored a number, and the original string is gone. In this article we'll explain why this happens and give you 5 ways to permanently disable the automatic conversion.
Why Excel does this
Excel tries to be smart: when you enter data into a 'general' format cell, it analyzes what the value looks like and picks a format on its own. The string '3-4' looks like the date 'April 3', '5/12' like 'December 5', '1.2.3' like 'February 1, 2003'. This check is not done by Windows or by your locale — it's the program's own behavior.
Inside Excel a date is just a number (see our converter): January 1, 2024 = 45292; the fractional part is the time of day. When the program decides you entered a date, it stores the number and applies the 'Date' format. After this, the original string '03-04' is no longer in the file — it stores 45385 (April 3, 2024) and a format that displays it nicely.
The main pain: Excel does not ask permission and does not warn you. People often notice the issue only when they export to CSV and see 45385 instead of the expected '03-04'.
What breaks most often
SKUs and product codes like 03-04, 12-25, 9-11 — turn into dates. Especially painful for marketplaces and e-commerce.
Release versions 1.2.3, 2.5.10 — become February 1, 2003 and May 2, 2010. Manually edited package.json files break.
Fractions written as '5/12' (proportions, ratios) — become 'December 5'.
Biological gene identifiers (SEPT1, MARCH2, OCT4) — turned into September 1, March 2, October 4. In 2020 scientists had to rename ~27 human genes because of this issue.
Long numbers (16-digit cards, EAN-13 barcodes, IMEI) — lose precision because Excel rounds to 15 significant digits, or get displayed as scientific notation (1.23E+12).
Phone numbers with leading zeros (095..., 098...) — lose the zero, because the value is stored as a number.
5 ways to stop the auto-conversion
- 1
Way 1 — prefix the value with an apostrophe
The fastest fix for one-off entries. Type ' (single quote) before the value: '03-04 or '1.2.3. Excel will store it as text; the apostrophe is hidden in the cell. Downside: must be typed manually for each cell, and only works for direct typing — not for paste from clipboard or import.
- 2
Way 2 — set the cell format to 'Text' first
Select the column or range → right click → Format Cells (Ctrl+1) → Number tab → 'Text' → OK. You MUST do this BEFORE entering data — once a value has been turned into a date, switching to text format will not bring it back: the original string is gone. Great for entire columns of SKUs or serial numbers.
- 3
Way 3 — Excel 2024 / Microsoft 365: new setting
Excel from Microsoft 365 (versions after October 2023) added a setting to disable auto-conversion: File → Options → Data → Automatic Data Conversion → uncheck all boxes ('Remove leading zeros and convert to a number', 'Keep the first 15 digits…', 'Convert digits surrounding E to a number…', 'Convert continuous letters and numbers to a date'). From now on Excel will stop guessing — particularly useful when importing CSV.
- 4
Way 4 — import CSV via the 'Get Data' wizard
Don't open a CSV by double-click — Excel applies auto-formats immediately. Use Data → Get Data → From File → From Text/CSV. In the Power Query editor, change problematic columns from 'Default' to 'Text' on the typing step. SKUs like 03-04 and versions like 1.2.3 will be imported as strings and survive.
- 5
Way 5 — Google Sheets: Format → Plain text
Google Sheets has the same issue and the same fix: select the column → Format → Number → Plain text. You can also adjust File → Spreadsheet settings → Locale (some date formats depend on locale). If your data is already corrupted — Ctrl+Z while the tab is still open, otherwise you'll have to paste it again.
If your CSV export contains '45245.5' instead of a date — our converter will give you the proper date back.
FAQ
Excel already turned my numbers into dates — can I recover them?
If you haven't saved the file yet — Ctrl+Z back to the original values. If you saved or closed it — sorry, Excel only stores the number and the format; the original string is gone. The only path is to open the source CSV/origin again and import it correctly (Way 4). That's why setting the format to 'Text' BEFORE entry is critical.
How do I open a CSV without auto-conversion?
Don't double-click the file. In Excel: Data → Get Data → From File → From Text/CSV → in the Power Query editor set problematic columns to 'Text' instead of 'Number' or 'Date'. In LibreOffice you'll get a dialog where you can pick the type per column up front.
Does Google Sheets have the same auto-conversion?
Yes, the same logic. Fix: Format → Number → Plain text on the column before pasting. Since 2023 Google Sheets can also detect the column type when importing CSV — there you can manually change 'Date' to 'Text'.
I don't have the 'Automatic Data Conversion' setting in Excel
This setting was added in Microsoft 365 in October 2023 and rolled out gradually through the first half of 2024. If you have Excel 2019/2021 or earlier — the setting isn't there; use the apostrophe or 'Text' format (ways 1-2).
How do I keep leading zeros in phone numbers and codes?
Set the column format to 'Text' (way 2) up front, or use a custom format like '00000000' (Format Cells → Number → Custom → pick a template with the right number of zeros). Excel will store the number but pad it with zeros to the chosen length on display.
Is this a bug, or was it designed this way?
Technically it's a 'feature' designed for the convenience of most users. But for developers, biologists, analysts and e-commerce teams it has been creating problems for years — so much so that in 2020 the scientific community renamed 27 human genes (SEPT1 → SEPTIN1, etc.) because Excel was corrupting them in analyses. In 2024 Microsoft finally added an opt-out, presumably after enough complaints.