If numbers keep changing to dates automatically in Excel or if Excel changes dates to random numbers then this post will help you fix the issue. As reported by some users, whenever they enter a number into a cell, it changes to date itself. The reason behind this issue is primarily the format of the cell.
Excel keeps changing Number to Date
If the numbers keep changing to dates in Microsoft Excel, you can use the following methods to fix the issue:
- Format cells as text.
- Insert an apostrophe before the number.
- Use Paste Special.
1] Format cells as text
The first thing you can do to prevent Excel from automatically changing a number to date is to change the format of problematic cells to text. Here are the steps to do that:
First, select all the problematic cells with numbers.
Next, right-click on the selected cells and choose the Format Cells option from the context menu. You can also press the CTRL+1 hotkey to open the Format Cells prompt.
Now, click on the Text category from the Numbers tab and press the OK button to save changes.
Once done, the issue will be resolved.
Read: Exception from HRESULT 0x800A03EC Excel error.
2] Insert an apostrophe before the number
You can also use an apostrophe before a number you want to enter in a cell. For example, if you want to add 9-7-2000 but it is getting changed to date 09-07-2000, you can add an apostrophe before the number like ‘9-7-2000. It will be shown as 9-7-2000.
3] Use Paste Special
If you are pasting numbers in the workbook from an external source, you can use the Paste Special feature to prevent numbers from changing to date. Copy the number and then click on the Paste > Paste Special option from the Home tab. In the opened prompt, choose Text or Value and press the OK button to enter the number.
Read: Fix There’s a problem with the clipboard Excel error.
Excel changing dates to random numbers
Some users have also reported that Excel keeps changing to random numbers in their workbooks. The primary cause of this problem is incorrect cell format. Also, if you are copying dates from external sources, Excel might misinterpret the date format and display random numbers.
Now, if you are also experiencing the same issue, you can follow the below solutions to fix the issue:
- Set the format of cells to Date.
- Use Date functions.
- Deselect the Show Formula option.
1] Set the format of cells to Date
If you have chosen an incorrect data format for cells where you want to insert dates, you will face this issue. So, make sure the cell format is set to Date. Here are the steps to do that:
- First, select all the cells with dates and press CTRL+1 to open the Formal Cells dialog.
- Now, click on the Date category from the Number tab.
- Next, you can select the date type, location, and calendar type to format dates in your workbook.
- Finally, click the OK button to save changes and see if the issue is fixed.
2] Use Date functions
Another thing you can do to fix this issue is to use Date functions to display dates in your Excel workbook. You can use syntax like =DATE(year,month,day) to do the same. For example, use =DATE(2024,2,14) to display the 14-02-2024 date in a cell.
3] Deselect the Show Formula option
You can also unselect the Show Formula option to prevent dates from changing to random numbers in Excel. To do that, go to the Formulas tab and check if the Show Formulas option under the Formula Auditing group is selected. If yes, deselect it and then see if the problem is fixed.
Read: How to stop Excel from rounding numbers?
How do I stop Excel from changing cell format automatically?
To prevent Excel from automatically formatting cells, you can customize Excel settings. Go to the File menu and choose Options. After that, move to the Proofing tab and click on the AutoCorrect Options button. Next, navigate to the AutoFormat As You Type tab and uncheck all the options. Finally, press the OK button to save changes.
How do I turn off auto change to date in Excel?
You can stop automatic data conversion in Excel by tweaking some settings. Open the File menu and select Options. Then, go to the Date tab and scroll to the Automatic Data Conversion section. Now uncheck all the options as per your requirements under this section and press the OK button.
Now read: Fix Number formatting not working in Excel.
