There are often situations in Excel when it seems like it does not act in the way that a user wants. In a lot of cases, this refers to data formatting.
As you are aware, dates are regarded as numbers in Excel, so it can be hard to prevent Excel to change the numbers to dates automatically. In the example below, we will show how to do it.
Prevent Excel From Changing a Number to a Date
There are several ways in which we can prevent Excel from doing this. The first date in Excel (number 1) refers to the 1st of January 1900. Every other date is just an addition to this number.
Having that in mind, we will insert the random numbers that range from the 1st of January 2022 till the 31st of January 2022, in the number format. These will be:
If we want to change these numbers to the dates, we could simply go to the Formatting options in the Home tab:
As seen, dates will be shown. This means that our data can be changed. To prevent this, we can:
- Format our cells as a TEXT. For our cells, we will copy and paste our data in column B, then select that range, right-click on these cells, and choose Format Cells:
When we get to the dialog box, we need to go to the Number tab, then choose Text in the Category list:
For the last step, we will click OK. Since these cells are not considered to be numbers anymore, they will not be automatically changed.
- Insert an apostrophe before the number. To make Excel observe a certain number as text, we can also add an apostrophe in front of the number. The apostrophe will not be visible but will prevent Excel from changing the number to date. When we do this, Excel will also give us an error explaining what went wrong, i.e. “Number stored as text”:
- Custom Format. We can also format the data ourselves, by following the same path as we did for the Text format. We will select the range, right-click on it, choose Format Cells, and then choose Custom in the Number tab. In the Type input box, we need to insert the “@” or 0 (zero) to format it as text:
We will click OK, and our range will be formatted as Text, which prevents Excel from changing this number into the date automatically.