In this guide we will provide 2 easy methods to stop Excel from automatically formatting numbers as dates.
We will cover:
- Why does Excel convert numbers to dates?
- Which number formats does this happen to
- How to prevent this with two different approaches:
- Method 1 – Text Formatting
- Method 2 – Using an apostrophe
Why does Excel convert numbers to dates?
Excel has been built to be as user friendly as possible for the majority of people. While Excel is a very powerful tool in it’s own right, with advanced formulas and ability to almost work as a fully fledged database, the bulk of Excel users don’t need this level of control.
Because of this, Excel makes a lot of assumptions based on the user input. Depending on what you enter in a cell it might think ‘This is a date!’ and applies the formatting automatically for you. Generally if you are typing something like 4/5 most users would be writing this in the context of the 5th of April, rather than four fifths as a fraction.
This is all based on what the developers have found to be the most common use cases, so for the most part this isn’t really a problem. It would likely be more annoying typing in dates and having to manually change the formatting to what looks like a date than the other way around!
Which number formats get converted to dates?
There are two common number formats that Excel tends to think we are trying to represent a date with.
These are – forward slahses and dashes
For example, in the below table we have three different numbers that we have used in the left most column, but if we were to type them directly into Excel without any preventative measures, we would end up with the dates that appear in the second column which is not ideal (unless you are typing dates – but then you probably wouldn’t be reading this guide!).
As we can see, something that might mean a score of 4 out of 5, or a fraction of four fifths gets converted to the 4th of May. In the second example maybe we are writing down the score of a soccer game that was 10 against 2, but Excel thinks we are entering the date of the 10th of February.
To make things even more confusing, if we were to highlight those cells, and change the format of them to ‘Number’, we would be left with the below!
This is because the way Excel stores dates is as a series of numbers, starting with the 1st of January 1900.
01/01/1900 is converted back to the number 1, the following day is 2, and so on all the way up to the 1st of January 2021 which becomes 44197!
Given we can’t simply force these values back to numbers we have two approaches top stop Excel changing numbers to dates we can take that we will run through.
Method 1 – Format as text
This first method to stop changing numbers to dates is the best approach as long as you treat it as a preventative fix, rather than something you apply later on. It also works well when we are entering a large number of values so we never have to worry about changing anything individually.
This approach is to simply format all of the cells as text. By using a text format Excel will know that you aren’t trying to enter any kind of numerical value such as a date, and will not try and be helpful or clever by automatically assigning it as something.
First, highlight all of the cells you plan on entering data in (or to be safe just select all with CTRL+A).
Next, under the drop down box that sits within the number section with in the home tab, select text at the bottom of the list:
Now, any number that you enter within these cells will simply appear exactly as you type them! Nice and easy.
Now let’s look at the second method.
Method 2 – Using an apostrophe
This second approach is luckily also quite simple, although does rely on us entering it every single time we type in a value that we don’t want converted to a date – and that is entering an apostrophe in the cell before the numbers themselves.
The apostrophe essentially tricks Excel into thinking that we are working with a string of text characters rather than a number that needs to be interpreted and converted to something else. Secondly, it also doesn’t appear at all visually in the cell, so you don’t have to worry about the spreadsheet looking weird with each number having that little apostrophe to the left of each number!
Using the same three examples at the top of this guide, let’s look at how this will work by starting off each cell with an apostrophe:
As we can see, each of the three outputs appeared exactly how we would have hoped!
On a side note, if you actually do for any reason want to start a string of characters with an apostrophe like I technically have done for the sake of the left column example, you just need to enter two of them. In cell B3 for example the actual value I entered here was ”4/5.
This approach is super handy if we have the occasional value we want to enter as it’s easy to quickly add the apostrophe before the number rather than go up and change the formating before typing anything.
Method 1 is far more efficient however when we are looking to enter a whole lot of numbers that might be interpreted as dates.
This sums up our guide on how to stop Excel changing numbers to dates automatically. Excel is a great tool, but can be a bit of a pain at times when it makes assumptions like it does here with date formatting. It means well though and as mentioned, it’s probably better to do this than the other way around where we would need to find ways to force formatting into dates!
For more handy Excel tips be sure to check out our Excel Tips page.