Excel guide · 5 min read
How to stop Excel changing numbers to dates
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | You meant | Excel stored | Shown as | Typed with apostrophe | |
| 2 | 1-30 | Jan 30, 2026 | 30-Jan | 1-30 | |
| 3 | 3/4 | Mar 4, 2026 | 4-Mar | ||
| 4 | 10-12 | Oct 12, 2026 | 12-Oct | ||
| 5 | 2-1 | Feb 1, 2026 | 1-Feb |
Download this sheet as an Excel practice file (.xlsx)
Why Excel does it
When you type into a cell, Excel tries to recognise numbers, percentages and dates so you can calculate with them. A dash or slash between two small numbers matches a date pattern, so a size range, a score, a part code or a fraction gets converted on Enter. With US date settings, 1-30 becomes 30 January of the current year and shows as 30-Jan; 3/4 becomes 4 March. With day-month settings (UK, Australia, most of Europe) the same entries can be read the other way round, or not as dates at all, which is why a file can behave differently on a colleague's computer.
The conversion isn't just a display format. The cell now holds a date serial number, the count of days since 1900. That's why changing the format afterwards doesn't bring your text back.
Result: 46052
46052: the "1-30" in B2 is really 30 January 2026, day 46,052. Set B2 to Text format now and you'd see 46052, not 1-30.
Fix 1: Format the cells as Text before you type
Best when you're about to enter a whole column of codes, sizes or scores. Text format tells Excel to store exactly what you type.
- Select the column (click its letter) or the range you'll type into.
- Home > Number Format box (it usually says General) > Text. Or press Ctrl+1 > Number tab > Text > OK.
- Now type 1-30, 3/4 or 10-12. They stay as typed and line up on the left like other text.
Do I lose anything by storing them as text?
Only if they're real numbers you want to add up. Codes, ranges and scores aren't, so text is right. If a text entry is a plain number, such as a code like 00123, Excel may mark it with a small green triangle ("Number stored as text"); for codes you can ignore it. If you later need the numbers inside, see the guide on numbers stored as text.
Fix 2: Type an apostrophe first
For a one-off entry, start it with an apostrophe: '1-30. The apostrophe tells Excel "this is text" and doesn't appear in the cell or in the value. It shows only in the formula bar. E2 above was typed this way.
Result: 4
4: the cell holds the four characters 1-30. The apostrophe isn't part of the value, so lookups and comparisons with "1-30" work.
Fix 3: Fractions: type 0 and a space
If you want 3/4 as the number 0.75 (for a measurement or a recipe), type 0 3/4: a zero, a space, then the fraction. Excel stores 0.75 and applies a fraction number format, so the cell shows 3/4 and the formula bar shows 0.75. A mixed number works the same way: 2 1/2 stores 2.5.
If 3/4 is a label you never calculate with, use Fix 1 or Fix 2 instead so it stays text.
Fix 4: Open CSV files with Data > From Text/CSV
Double-clicking a CSV file opens it with automatic conversion, so a column of codes like 1-30 or 3-4 arrives as dates before you can do anything. Import it instead and tell Excel to leave the column alone.
- In a blank workbook, Data > From Text/CSV, pick the file and click Import.
- In the preview, open Data Type Detection and choose Do not detect data types. Everything comes in as text. Then click Load.
- Or click Transform Data, select the column, and on the Home tab set Data Type to Text, then Close & Load. This keeps other columns (amounts, real dates) detected normally.
The Automatic Data Conversion settings
Recent versions of Excel for Microsoft 365 have File > Options > Data > Automatic Data Conversion. Its options cover removing leading zeros, long numbers shown in scientific notation, digits around the letter E, and letters run together with numbers becoming dates (MARCH1 turning into 1-Mar). None of them stops 1-30 or 3/4 from becoming a date, so Text format and the apostrophe are still the fixes for that. Older versions don't have the section at all.
Fix 5: Get the original back after it's converted
Ctrl+Z straight after typing removes the whole entry; retype it with an apostrophe. For a column that's already converted, rebuild the text from the date with TEXT, using the same pattern you typed: "m-d" for dashes, "m/d" for slashes (US order). Then Copy and Paste Special > Values over the original, with the column formatted as Text first.
Result: spills 4 rows × 1 column
| 1-30 |
| 3-4 |
| 10-12 |
| 2-1 |
1-30, 3-4, 10-12 and 2-1 spill down. For entries typed with a slash, use "m/d": =TEXT(B3,"m/d") gives 3/4.
When the original can't be rebuilt
If the file was saved as CSV after the conversion, the CSV holds whatever was displayed (30-Jan), and the original typing is gone from that file. The same goes for entries Excel read as month and year, or entries that had leading zeros. Go back to the source file and import it as in Fix 4.
Common mistakes
Changing the format to Text after typing
The value is already a date serial number, so you see 46052 instead of 1-30. Rebuild it with TEXT (Fix 5) or retype it.
Typing the apostrophe into the formula, not the cell
The apostrophe trick is for typed entries. In a formula, a text result is already text: ="1-30" returns 1-30 and stays that way.
Double-clicking CSV files with codes in them
Import with Data > From Text/CSV and set the column to Text before loading.
Rebuilding with the wrong date order
TEXT(B2,"m-d") matches US typing. If your computer reads dates day first, the entry was converted the other way round, so use "d-m".
FAQ
How do I stop Excel from auto-formatting numbers as dates?
Format the cells as Text before you type, or type an apostrophe first ('1-30). There's no single setting that turns date recognition off for typed entries.
Why does Excel change 1-2 to 2-Jan?
A dash between two numbers that could be a month and a day matches Excel's date pattern, so it stores the date and shows it in a short date format. Type '1-2 or format the cell as Text first.
How do I type a fraction in Excel without it becoming a date?
Type a zero, a space and the fraction: 0 1/2. Excel stores 0.5 and shows 1/2. If the fraction is just a label, type '1/2 instead.
Can I undo a number that turned into a date?
Right after typing, press Ctrl+Z and retype it as text. For a whole column, recreate the text with =TEXT(cell,"m-d") (or "m/d") and paste the results back as values.
Why are my product codes dates when I open a CSV?
Opening a CSV by double-clicking converts anything that looks like a date. Use Data > From Text/CSV and choose Do not detect data types, or set that column to Text in Power Query.
Functions used
Related guides
- How to stop Excel AutoCorrect changing your text
- How to convert numbers stored as text in Excel
- Excel date format not changing? Why, and how to fix it
- How to convert text to a date in Excel
- How to abbreviate large numbers in Excel: thousands (K), millions (M), billions (B)
- Convert seconds to hh:mm:ss in Excel
Updated