Excel changes some numbers to dates because it is built to make entering dates easy, so an entry like 12/2 becomes 2-Dec. Microsoft says there is no way to turn this off completely, but you can prevent it by formatting the cells as Text before you type or by starting the entry with an apostrophe (‘).
This guide follows Microsoft’s support articles on stopping numbers from changing to dates, automatic data conversion options, and undoing automatic formatting. Steps apply to Excel for Microsoft 365, Excel 2024, 2021, 2019, and 2016, plus Excel for the web where noted.
Why Does Excel Change Numbers to Dates?
When a cell uses the General format, Excel tries to guess what you meant, and entries that look like dates, such as 12/2 or 1/2, are stored as dates instead of the text you typed. Microsoft’s examples include fractions: 1/2 turns into 2-Jan and 3/4 into 4-Mar. Codes, ranges, and fractions are the usual victims.
How to Stop Excel From Changing Numbers to Dates
- Select the cells where you will type the numbers.
- Press Ctrl+1 (the 1 in the number row above the letter keys) to open Format Cells.
- Select Text, then select OK.
- Type your numbers.
Excel keeps each entry exactly as you typed it. In Excel for the web, select the cells and choose Home > Number Format > Text instead.
Quick Fixes for a Few Numbers
- Type an apostrophe first, like ‘1/47 or ’11-53. The apostrophe does not show in the cell after you press Enter.
- Type a space first. This works too, but the space stays in the cell.
- For fractions, type a zero and a space first, like 0 1/2. Microsoft says the zero disappears and the cell becomes the Fraction number type.
Microsoft recommends the apostrophe over the space if you plan to use lookup functions such as MATCH or VLOOKUP on the data, because those functions overlook the apostrophe.
The entry stays as typed. Microsoft notes you may see a small green triangle in the corner of the cell, because Excel flags a number stored as text as an error. You can ignore it, or select the cell, select the warning box, and choose Ignore Error.
Use Excel’s Automatic Data Conversion Settings
Newer versions of Excel (Microsoft 365, 2024, and 2021) have a narrower setting. It cannot stop 12/2 from becoming a date, but it can stop entries that run letters and numbers together, such as JAN1, from converting.
- Select File > Options > Data.
- Under Automatic Data Conversion, clear Enable all default data conversions below.
- Clear Convert continuous letters and numbers to a date, then select OK.
Values like JAN1 stay as text. Microsoft warns that entries with spaces or other characters, such as JAN 1 or JAN-1, may still be treated as dates. The same section lets you keep leading zeros, keep all digits of long numbers, and stop numbers with an E from turning into scientific notation.
The AutoCorrect AutoFormat As You Type options don’t help here. Microsoft says the Number group on the Home tab controls how numbers and dates appear. Automatic hyperlinks are a separate setting. To stop them, go to File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and clear Internet and network paths with hyperlinks. Our guide shows how to turn off automatic hyperlinks in Excel.
Stop Dates When You Paste or Import Data
Pasted and imported data gets the same date guessing. Format the destination columns as Text before you paste, or use Data > From Text/CSV and set the affected columns to Text in the import preview. For CSV files, Microsoft says Excel can show a dialog listing the automatic conversions it is about to make, which is controlled by the notify me of any automatic data conversions option in the same Data settings.
Preserve Leading Zeros and Long Identifiers
Excel drops leading zeros from numbers and keeps only 15 significant digits. Store account numbers, ZIP codes, and other identifiers as text instead:
- Select the destination cells and press Ctrl+1.
- Choose Text and select OK.
- Type, paste, or import the codes, then compare a few with the source.
A number format can’t bring back digits Excel already dropped, so re-enter those codes from the original source. Keep quantities you calculate with as numbers. Microsoft’s leading-zero and large-number guide explains the 15-digit limit.
Can I Get Back Numbers That Already Became Dates?
Usually not by changing the format. Once Excel converts an entry such as 1-5 or 3/4, it stores a date serial number and the original characters are gone, so switching the cell back to General shows that serial number instead. Re-enter those values in cells already formatted as Text, start them with an apostrophe, or re-import the source file. Try pressing Ctrl+Z right away if you catch a conversion as it happens.
Frequently Asked Questions
How can I tell if a number is stored as text?
Microsoft says a number that is left-aligned in a cell usually is not formatted as a number. You may also see the green error triangle described above.
Why does Excel change 1e9 into 1.00E+09?
That is scientific notation, not a date, but the fix is similar. Microsoft says to type an apostrophe first, like ‘1e9, or turn off Convert digits surrounding the letter “E” into a number in scientific notation in the Automatic Data Conversion settings.
More Excel Guides
- Why Excel shows ##### and how to fix column width
- How to calculate percentage in Excel
- How to keep the second decimal place in Excel
Matthew Burleigh is the founder, head writer, and editor of Solve Your Tech, which he launched in 2012. He has written technology tutorials since 2008 and worked in IT since 2003, including small-business support, IT management, and consulting. He holds Bachelor’s and Master’s degrees in Computer Science from Arizona State University.
Matthew writes practical guides for Windows, iPhone and iPad, Microsoft Office, Outlook, Google apps, and Android. Solve Your Tech’s guides are checked step by step against the device or app maker’s own documentation and tested on his own devices whenever he has the hardware or software, with clear steps, sources, and update dates.
Read more about Matthew and how Solve Your Tech writes and updates its guides, or report a correction.