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 (Windows and Mac), Excel 2024, 2021, 2019, and 2016, plus Excel for the web where noted. The Text format and apostrophe fixes work the same way in Excel 2013 and Excel 2010.
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.
Behind the scenes, Excel stores every date as a serial number that counts days, and the cell format only controls how that number looks. Once your entry becomes a date, Excel also fills in the current year for you.
These are typical entries Excel converts on a US English PC:
| You type | Excel shows | Why |
|---|---|---|
| 12/2 | 2-Dec | Month and day separated by a slash |
| 1/2 | 2-Jan | A fraction looks like month/day |
| 5-10 | 10-May | A range looks like month-day |
| 3/4/24 | 3/4/2024 | A full date with a two-digit year |
| JAN1 | 1-Jan | Letters and numbers that read as a month and day |
| 13-15 | 13-15 (no change) | 13 is not a valid month |
What counts as a date depends on the date order in your computer’s regional settings. On a PC set to day/month order, 3/4 becomes April 3 instead of March 4, so a shared file can behave differently on a coworker’s computer.
When you do want real dates, such as birthdates, that same behavior lets you calculate age from a birthdate in Excel.
How to Stop Excel From Changing Numbers to Dates
Format the cells as Text before you type. This is the most reliable fix for codes, part numbers, ranges, and anything else you won’t calculate with.
- Select the cells where you will type the numbers. To cover a whole column, click its letter at the top.
- Press Ctrl+1 (the 1 in the number row above the letter keys) to open Format Cells. On a Mac, press Command+1.
- Select Text, then select OK.
- Type your numbers.
Excel keeps each entry exactly as you typed it, aligned to the left. In Excel for the web, select the cells and choose Home > Number Format > Text instead.
Format the cells first. Changing a cell to Text after Excel has already converted an entry doesn’t bring your original characters back; it shows the date’s serial number instead. Also keep in mind that Text cells don’t take part in math, so leave columns you add, average, or sort numerically in a number format.
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, but you can still see it in the formula bar.
- 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. The zero disappears and the cell becomes the Fraction number type.
Use the apostrophe rather than 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. 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.
Keep Fractions as Real Numbers
If you need fractions you can calculate with, use the Fraction format instead of Text. Microsoft’s fraction format guide says to apply it before you type:
- Select the cells and press Ctrl+1.
- In the Category list, select Fraction.
- Pick a style under Type, such as Up to one digit or As quarters, then select OK.
- Type your fractions, such as 3/4.
The cell shows 3/4 but holds 0.75, so formulas work. Excel reduces fractions to the lowest terms, so 8/12 shows as 2/3. For a fixed denominator, use a custom code such as # ?/16.
Use Excel’s Automatic Data Conversion Settings
Newer versions of Excel have a narrower setting. Microsoft’s automatic data conversion steps list it for Excel for Microsoft 365 and Excel 2024 on Windows and Mac, and Excel 2021 on Windows also has it. 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. On a Mac, choose Excel > Preferences > Edit.
- 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. 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. These settings apply when you type, paste, open a CSV or TXT file, use Find and Replace, or run Text to Columns, but not to data you load with Power Query.
The AutoCorrect AutoFormat As You Type options don’t help here, because the Number group on the Home tab controls how numbers and dates appear. Automatic hyperlinks are a separate setting under File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type, where you can clear Internet and network paths with hyperlinks. Here’s 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.
- Pasting from a web page, email, or another workbook: Format the destination columns as Text first, then use Home > Paste > Paste Values so the source formatting doesn’t replace your Text format. The same idea helps when you copy Word text or tables into Excel.
- Opening a CSV file: Double-clicking a CSV lets Excel guess every column. Instead, open a blank workbook and use Data > From Text/CSV. Select Transform Data, select the affected column, choose Data Type > Text on the Home tab, select Replace current if asked, and select Close & Load. Microsoft’s leading-zero and large-number guide shows this route.
- Conversion warning: For CSV files, 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 Data settings.
If double-clicked CSV files keep opening in Excel and you’d rather pick the app yourself, read about opening CSV files with Excel by default for the file association 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, and keep IDs and real dates in separate columns. The Microsoft guide linked above 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. Try pressing Ctrl+Z right away if you catch a conversion as it happens. Otherwise, re-enter those values in cells already formatted as Text, start them with an apostrophe, or re-import the source file.
When the source is gone, a formula can rebuild simple entries from the month and day Excel kept. With a converted value in A2:
- For codes or ranges like 5-10, enter
=MONTH(A2)&"-"&DAY(A2)in a blank column. - For fractions like 3/4, enter
=MONTH(A2)/DAY(A2)and give the column the Fraction format.
Copy the results, then use Paste > Paste Values over the original cells (formatted as Text for codes). Check the results against what you expected. This can’t restore leading zeros, a year you didn’t type, or entries that started with a day in a day/month region.
How to Change the Date Format in Excel
To change how dates look, select the cells, press Ctrl+1, and pick a style under Date, or type your own code under Custom.
- Select the cells that contain the dates.
- Press Ctrl+1 to open Format Cells, then select the Number tab.
- In the Category list, select Date and choose a style under Type. The Sample box previews it.
- For a style that isn’t listed, select Custom in the Category list and enter a code in the Type box. For example,
mm/dd/yyyyshows 03/07/2026, anddddd, mmmm d, yyyyshows Saturday, March 7, 2026. - Select OK.
In the codes, m and mm show the month as 3 or 03, mmm shows Mar, and mmmm shows March. Days work the same way with d, dd, ddd (Sat), and dddd (Saturday), and yy or yyyy shows a two or four digit year. Microsoft’s date format guide lists every code.
Date styles that start with an asterisk (*) in the Type list change when someone changes the regional date settings in Windows. Pick a style without the asterisk, or use a custom code, if the format must stay the same on every PC. If a cell shows ##### after the change, double-click the right edge of the column heading to widen the column. A cell that keeps its look no matter which format you choose holds text that only looks like a date, so re-enter it as a date.
Frequently Asked Questions
Is there a setting to turn off date conversion completely?
No, there is no way to turn it off. The Automatic Data Conversion setting only covers letter and number combinations like JAN1, so use the Text format or an apostrophe for entries like 12/2.
How can I tell if a number is stored as text?
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.
Left-aligned dates are stored as text too, which is why they won’t sort by date in Excel Online until you convert them.
Why does Excel change 1e9 into 1.00E+09?
That is scientific notation, not a date, but the fix is similar. 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.
Why did the same file show different dates on another computer?
The other computer likely uses a different date order in its regional settings, so an entry like 3/4 was read as a different month and day. Store codes as Text so they don’t depend on regional 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.