Cellwise Start free

Excel guide · 5 min read

How to stop Excel changing numbers to dates

Quick answerExcel turns anything that looks like a date (1-30, 3/4, 10-12) into a real date as you type it. To keep what you typed, format the cells as Text before you type (Home > Number Format > Text), or start the entry with an apostrophe ('1-30). For fractions, type a zero and a space first (0 3/4). For CSV files, import with Data > From Text/CSV and set the column to Text instead of double-clicking the file.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDE
1You meantExcel storedShown asTyped with apostrophe
21-30Jan 30, 202630-Jan1-30
33/4Mar 4, 20264-Mar
410-12Oct 12, 202612-Oct
52-1Feb 1, 20261-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.

=B2*1

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.

  1. Select the column (click its letter) or the range you'll type into.
  2. Home > Number Format box (it usually says General) > Text. Or press Ctrl+1 > Number tab > Text > OK.
  3. 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.

=LEN(E2)

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.

  1. In a blank workbook, Data > From Text/CSV, pick the file and click Import.
  2. In the preview, open Data Type Detection and choose Do not detect data types. Everything comes in as text. Then click Load.
  3. 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.

=TEXT(B2:B5,"m-d")

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

Updated