Why does a spreadsheet turn my numbers into dates, and how do I stop it?
The spreadsheet is guessing what you meant and guessing wrong. How to stop it at import, how to protect the values it mangles, and what is already lost.
- Difficulty
- beginner
- Time
- 20 min
- Read
- 4 min
- Safety
- caution
Short answer
Spreadsheets interpret what you type. Anything that looks like a date becomes one, leading zeros are dropped from numbers, long digit strings are shown in scientific notation, and very long numbers lose their last digits entirely. Format the cells as text before typing or pasting, or use the import wizard when opening a CSV and set those columns to text. Once a value has been converted and saved, the original is usually gone.
This behaviour causes real damage in real work — it is well known for corrupting scientific data, and it quietly mangles reference numbers in household admin every day. The fix is always to tell the spreadsheet the column is text before the data arrives, not after.
Safety
Step by step
- Recognise the four conversions.Anything resembling a date becomes a date and displays as one. Leading zeros are stripped, so an account number starting with zero loses it. Long digit strings become scientific notation. Numbers longer than fifteen digits lose precision — the trailing digits become zeros and are not recoverable.
- Format the cells as text before entering anything.Select the column, set its format to Text, then type or paste. The cell then stores exactly what you give it. Doing this afterwards does not undo a conversion — it just displays the already-changed value.
- Use the import wizard for CSV files.Do not double-click a CSV to open it. Use the spreadsheet's import or get-data function, which lets you set each column's type before the data lands. Set anything that is an identifier rather than a quantity to text. This is the single most useful habit here.
- Prefix with an apostrophe for one-off entries.Typing an apostrophe before a value forces the spreadsheet to treat it as text. The apostrophe is not part of the stored value and does not appear in printing. Convenient for a handful of cells, impractical for a column.
- Check the data after any import.Look specifically at reference numbers, postcodes, phone numbers, product codes, and anything with a leading zero or a hyphen. Sort the column and look at both ends; converted values stand out immediately because they sort differently.
- Know what cannot be undone.A date conversion loses the original text — the cell now holds a date and no record of what was typed. Precision lost beyond fifteen digits is gone. If the source file still exists, reimport it correctly; if not, the data must be re-obtained.
- Watch out on export as well.Saving a spreadsheet as CSV writes what is displayed, so a mangled value is exported mangled. Check the exported file in a plain text editor if it is going anywhere important.
- Consider not using a spreadsheet for identifiers.If you are handling lists of reference numbers regularly, a spreadsheet is the wrong tool and will keep doing this. A plain text file, or a database, stores what you give it.
If it doesn't work
Product codes have turned into dates
Cause: Automatic date recognition on values like 3-4 or 12/5 — Fix: Reimport the source with those columns set to text. There is no way to recover the original from the converted cell alone.
Account numbers lost their leading zeros
Cause: Stored as a number, where leading zeros have no meaning — Fix: Reimport as text. As a temporary display fix, a custom number format can pad with zeros, but the underlying value is still a number and will export without them.
Long numbers show as 1.23E+15
Cause: Scientific notation applied to a large number — Fix: If it is under fifteen digits, widening the column and formatting as text restores the display. Beyond fifteen digits the trailing digits are genuinely gone.
Dates are in the wrong order — day and month swapped
Cause: The file was written in one regional convention and read in another — Fix: Set the locale in the import wizard, or import the date column as text and convert deliberately. Ambiguous dates below the thirteenth are silently wrong, which is worse than an error.
Postcodes have been altered
Cause: Some UK postcode formats resemble other data types, and spaces are inconsistently handled — Fix: Import as text. Check a sample against the source rather than assuming.
Questions people ask
Can I turn the guessing off completely?
Not entirely in most spreadsheets, though recent versions of Excel have added options to reduce automatic data conversion. Formatting as text before entry, and using the import wizard, remain the reliable approach.
Does this happen in Google Sheets too?
Yes, with the same causes and largely the same fixes. Formatting as plain text before pasting, and setting the locale, are the equivalents.
Why does it happen at all?
Because spreadsheets are built to save typing for people entering dates and numbers, and cannot tell an identifier from a quantity. The convenience is real; the cost is that data arrives changed.