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 — turn off Automatic Data Conversion in Microsoft 365
In current Microsoft 365 builds that provide this option, go to File → Options → Data → Automatic Data Conversion. Clear the conversions you do not want, such as removing leading zeros, limiting precision after 15 digits, interpreting E notation, or changing date-like values. Availability varies by platform, build and update channel; the setting is especially useful when working with CSV files.
- 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
Availability depends on your operating system, Excel build and Microsoft 365 update channel. Update Office and check the Data section again. If the option is absent from your perpetual or older Excel version, use a leading apostrophe, preformatted Text cells, or the Get Data import flow.
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?
It is automatic formatting intended to make ordinary data entry easier. However, it has caused problems for developers, biologists, analysts and e-commerce teams for years; in 2020 the scientific community even renamed 27 human genes, including SEPT1. Current Microsoft 365 versions can disable several of these conversions.