Excel has some really smart features that can be really useful in many cases (and sometimes it could be frustrating).
One such area is when you enter numbers in Excel. Sometimes, Excel automatically changes these numbers to dates.
For example, if you enter 1/2 in Excel, Excel will automatically change this to 01-02-2020 (or 02-01-2020 if you’re in the US)
Similarly, if you enter 30-06-2020, Excel assumes you want to enter a date, and it changes this to a date (as shown below)
While this may be what you want in most cases, but what if I don’t want this. What if I simply want the exact text 30-06-2020 or the fraction 1/2.
How do I stop Excel from changing numbers to dates?
That’s what this tutorial is about.
In this Excel tutorial, I will show you two really simple ways to stop Excel from changing numbers to text.
But before I show you methods, let me quickly explain why Excel does this.
Why does Excel Changes Numbers to Date?
While it can be frustrating when Excel does this, it’s trying to help.
In most cases, when people enter numbers that can also represent valid date formats in Excel, it will automatically convert these numbers into dates.
And this just doesn’t mean that it changes the format, it actually changes the underlying value.
For example, if you enter 3/6/2020 (or 6/3/2020 if you’re in the US and using the US date format), Excel changes the cell value to 44012, which is the numerical value of the date.
Also read: How to Convert Numbers to Text in Excel
Stop Excel from Changing Numbers to Dates Automatically
The only way to stop Excel from changing these numbers (or text string) into dates is by clearing letting it know that these are not numbers.
Let’s see how to do this.
Change the format to text
The easiest way to make sure Excel understands that it’s not supposed to change a number to date is by specifying the format of the cell as Text.
Since dates are stored as numbers, when you make the format of a cell text, Excel will understand that the entered number is supposed to be in the text format and not to be converted into a date.
Below is how you can stop Excel from changing numbers to dates:
- Select the cell or range of cells where you want to make the format as Text
- Click the Home tab
- In the Number group, click on the dialog box launcher icon (or you can use the keyboard shortcut Control + 1).
- In the Format Cells dialog box, in the category option, click in Text
- Click OK
The above steps would change the cell format to text. Now when you enter any number such as 30-06-2020 or 1/2, Excel will not convert these into date format.
You can also open the Format Cells dialog box by selecting the cell, right-clicking on it and then clicking on the Format Cell option.
This method is best suited when you have to change the format of a range of cells. If you only have to do this for a couple of cells, it’s best to use the apostrophe method covered next
Also read: How to Convert Serial Numbers to Dates in Excel (2 Easy Ways)
Add an Apostrophe before the Number
If you only have to enter a number in a few cells and you don’t want Excel to change it to date, you can use this simple technique.
Just add an apostrophe sign before you enter the number (as shown below).
When you add an apostrophe at the very beginning, Excel considers that cell as text.
While you would not see the apostrophe sign, you can visually see that the numbers would be aligned to the left (indicating that it’s text).
By default, all numbers align to the right and all text values align to the left.
Another benefit of using the apostrophe sign is that you can still use the lookup formulas (such as VLOOKUP or MATCH) using the value in the cell. The apostrophe will be ignored by these functions.
You can also use this method to change existing dates into text. For example, if you have 30-06-2020 in a cell, you can simply add an apostrophe (‘) at the beginning and it will be changed the date to text
Sometimes, you may see a green triangle at the top-left part of the cell, which indicates that numbers have been stored as text in those cells. But since that’s exactly what we want in this case, you can ignore those green triangles, or click on it and then select the Ignore Error option to make it go away.
While these methods are great, what if you have a column full of dates that you want to use as a text and not as dates. Here are some simple methods you can use to convert date to text in Excel.
I hope you found this Excel tutorial useful!
You may also like the following Excel tutorials:
- How to Remove Cell Formatting in Excel
- How to Remove Time from Date/Timestamp in Excel
- How to Wrap Text in Excel
- Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds)
- How to Stop Excel from Rounding Numbers
- How to Display Numbers as Fractions in Excel (Write Fractions in Excel)
- How to Compare Dates in Excel (Greater/Less Than, Mismatches)
4 thoughts on “How to Stop Excel from Changing Numbers to Dates Automatically”
All of the solutions which suggest changing the format to text miss the point that you can then no longer use the data for calculations. Anything pasted into excel from say a web site will change numbers to text and they can no longer be used in calculations.
it is an annoying and ridiculous (and time consuming feature to cope with) of the latest excel software. None of the suggested solutions I have found actually work apart from time wasting manual changing.
These can reverse the format, but it doesn’t give the original value. How can you simply prevent Excel from doing this?
Entering extra spaces etc. helps if you are enetring values manually. However, there’s a bigger problem if you are importing data from a text- or .csv-file.
I have no solution for preventing excel chancing numerical values with decimal points to dates. How can I do that??
another way, which avoids the apostrophe, is to enter =”1/2″, then copy that and paste as values; no need to format anything – useful if you have many values to enter which can be formularised (you can’t enter a formula in a cell formatted as text)