Excel has autocorrect data input turned on by default. After pasting the number into the cell, it is automatically replaced with the date. If the Excel number has a similar length as the date, it will go crazy and change the number to a date.
This changes the contents of the cell. A strange date appears instead of a number. These are often dates from hundreds of years ago or from the future.
Let’s see how to disable AutoFormat for dates in Excel. I know a few ways I want to share with you.
Table of Contents
Disabling auto-correction
The easiest way to turn off number-to-date conversion is to turn off AutoCorrect. AutoCorrect is on, but you can turn it off.
In order to disable autocorrect, you need to find the responsible option in the ribbon menu.
Click File > Options > Proofing. Click the AutoCorrect Options button. The AutoCorrect window appears.
Go to the AutoFormat As You Type tab. Deselect all checkboxes.
This change will have two important consequences for you:
- Excel will stop recognizing numbers as dates automatically, which will solve the problem you are looking for a solution here
- all other autocorrect options will not work
Set the data type manually
I have another solution for you. If you don’t want to turn off autocorrect, you can do something else.
Suppose you have a table into which you will paste numeric data. Let’s not let Excel convert numbers to dates.
Right-click on the cell where you want to paste the data. Select the Format Cells option.
Manually select the Number format.
The data format in your cell has been set to numeric and will remain so.
When pasting data into a cell, use the paste special option as values. To do this, when pasting data, right-click on the cell to which you are pasting. From Paste Options, select Values.
The goal has been achieved. Data format did not change to date.
The downside to this method is that it is quite cumbersome, because you have to set the data format manually each time and then paste specifically.
Changing the data format after pasting
Let’s move on to another way to deal with the tedious swapping of numbers to dates.
Suppose you pasted the data and the numbers changed to dates. To change the contents of a cell to numbers you should remove the cell formatting.
In the earlier ways, we changed the data format before pasting the data. In this way, we will change the data format after pasting.
To remove cell formatting, select the corresponding option in the ribbon menu. On your right side you can find the Clear button. Click on it and select Clear Formats.
Tip: The keyboard shortcut is ALT + H + E + F
When you use this option, the cell’s formatting is removed and the cell’s contents change to a number.
This method has two disadvantages:
- Every time after pasting, you need to remember to remove cell formatting
- After using this method, you will remove any kind of cell formatting. Not only the data format will change. You will also delete the font format, border, color, etc.
Pasting an apostrophe or space
I know one more way. This time it’s a cheat on Excel. Excel will not change the data type when it recognizes something in a cell that it cannot match with any data type it knows.
There are several such characters that you can enter into the cell just before entering the numbers.
- apostrophe
- space
- zero
When you type them into a cell and then paste the number, Excel won’t change it to the date. Excel will just go crazy because it doesn’t associate these characters with a date.
The downside of this method is that you type unnecessary and nonsensical characters into the cells. But maybe this idea is worth applying?
