How to Stop Excel Changing Numbers to Dates

Excel changing numbers to dates is one of its most common surprises: you type 3/4 or 10-12 and get 4-Mar or 12-Oct. To stop it, format the cells as Text before you type, or put an apostrophe in front of the entry. This guide explains why it happens, how to prevent it when typing and importing, and how to recover the values Excel has already changed.

Why does Excel change numbers to dates?#

Excel checks each entry as you type it, and anything that looks like a date in your regional settings is stored as a date. That includes:

• Slashes and dashes: 3/4, 1-2, 10-12, 2/14.
• Month names with numbers: Jan-24, Mar1, Sep 3.
• Scores and ranges: a match score of 2-1 or a size range of 6-8.

Once converted, the cell no longer holds what you typed. It holds a date serial number, the count of days since 1 January 1900. So 3/4 typed in 2026 becomes 46085, which is 4 March 2026 in US settings, and Excel shows it as 4-Mar. With day-first settings, the same entry becomes 3 April. The guide to why Excel dates are numbers explains the serial number system.

Fix 1: Format the cells as Text before you type#

The reliable fix is to tell Excel the cells hold text before any data goes in:

1. Select the cells or the whole column.
2. Press Ctrl+1 (Mac: ⌘1) to open Format Cells.
3. On the Number tab, choose Text and click OK.
4. Now type or paste your values.

Excel keeps 3/4 as the text 3/4. The same trick protects codes with leading zeros, such as 00451, which Excel would otherwise turn into 451; see how to keep leading zeros in Excel.

The order matters. Formatting a cell as Text after Excel has converted the entry doesn't bring back 3/4: it shows the serial number 46085 instead, because that's what the cell now contains.

Fix 2: Type an apostrophe first#

For a few cells, type an apostrophe before the entry:

'3/4

The apostrophe tells Excel to store the rest as text. It isn't shown in the cell, only in the formula bar. You may see a small green triangle warning about a number stored as text. That's fine for codes; click the warning and choose Ignore Error if it bothers you.

A space before the entry also stops the conversion, but avoid it. The space becomes part of the value, and lookups or COUNTIF won't match it to the same code without the space.

Fix 3: Type fractions with a leading zero#

If you want the number three quarters, not the text 3/4, type a zero and a space first:

0 3/4

Excel stores 0.75 and shows it as 3/4 with a fraction number format. For a mixed number, type 1 3/4 to get 1.75. Use this when you need to calculate with the values, because text can't be added up.

How to stop dates when you import a CSV file#

When you open a CSV file by double-clicking it, Excel converts each column on its own, so part codes and IDs can become dates before you see them. Import the file instead and choose the column types:

1. Go to Data > From Text/CSV and pick the file.
2. Click Transform Data to open Power Query.
3. Click the type icon in the column header and choose Text.
4. Click Close & Load.

Recent versions of Microsoft 365 also have settings under File > Options > Data > Automatic data conversion, where you can turn off some conversions, such as removing leading zeros or turning text like MAR1 into a date. These don't stop entries like 3/4 from becoming dates, so formatting as Text is still the fix to rely on. For dates that import in the wrong order, see why Excel swaps the day and month.

How to get your original numbers back#

If you notice straight away, press Ctrl+Z (Mac: ⌘Z) to undo the entry, format the cells as Text and type the values again.

If the data was changed earlier, rebuild the original text from the date with TEXT. TEXT turns a number into text using a format code. If the codes were typed month first, like 3-4:

=TEXT(B2,"m-d")

m is the month and d is the day, both without leading zeros. Use "m/d" if the originals had slashes, or "d-m" if your Excel reads dates day first. Then replace the column: copy the results and use Paste Special > Values (Windows: Ctrl+Alt+V; Mac: Control+⌘+V) over the original cells, formatted as Text first. The TEXT function guide lists the date codes.

One limit: if Excel guessed a year, such as turning 1-50 into January 1950, the date still holds the right pieces but you need a different code, like "m-yy". Check a few rows before you replace the column.

What if you need the values as numbers again?#

Sometimes the data really is numeric, such as quantities typed with a slash that should have been a decimal. TEXT returns text, so turn it into a number with VALUE when you need to calculate:

=VALUE(TEXT(B2,"m"))

That returns the month part, 3, as a real number. The VALUE function guide covers the details. If a date shows as a number rather than the other way round, that's the opposite problem: see why an Excel date is showing as a number and the guide to number formats for currency and percentages.

Practise it#

Knowing that a date is just a number formatted to look like a date makes this problem easy to fix. Practise how Excel stores dates with DATE, YEAR, MONTH and DAY, then practise TEXT and VALUE in a live spreadsheet: you turn dates into text and text into numbers, and every formula is checked as you type. The exercise on this page is a good first step.