Sometimes, when we input dates or do some operation with the dates, they change into random numbers. As a result, we cannot see the original dates. In this article, we will show you 3 possible solutions to fix the problem of Excel changing dates to random numbers. If you are also curious about the solutions, download our practice workbook and follow us.
Excel Changing Dates to Random Numbers: 3 Possible Solutions
We have a dataset of 10 employees of a company. Their name and joining dates are shown on the dataset. But, when we tried to calculate the promotion date, it returned some random numbers instead of the dates.
Now, we will demonstrate to you how to solve this problem, Excel autonomously changing dates to random number problems.
All the operations of this article are accomplished by using the Microsoft Office 365 application.
Solution 1: Modify Cell Format
In this solution, we have to set the proper formatting of our cell to stop the changing of dates to random numbers. The dates of column D show as some random numbers instead of the date value. The steps of the solution process are given below:
- First of all, select the range of cells D5:D14.
- Now, in the Home tab, select the drop-down arrow from the Number group and choose the Short Date format.
- You will see all the random numbers in column D converted into original dates.
Thus, we can say that our formula works perfectly, and we are able to fix the problem of Excel changing dates to random numbers.
Solution 2: Use Paste Special Feature
Sometimes, when we paste the dates as a Value, Excel may show some random numbers instead of showing the primary dates. We can solve this trouble with the help of Microsoft Word. We will copy the range of cells D4:D14 in a new worksheet. The steps of this method are given as follows:
- At first, select the range of cells D4:D14.
- Now, press ‘Ctrl+C’ to copy the data.
- Then, launch Microsoft Word on your device and choose the Blank Document option.
- After that, in the Home tab, click on the drop-down arrow of the Paste > Paste Special option from the clipboard group.
- As a result, a small dialog box titled the Paste Special will appear.
- Select the Unformatted Text option and click OK.
- You will get the dates in your Word file.
- Again, select the whole data range and press ‘Ctrl+C’ to copy the data.
- Now, go to the desired sheet of Microsoft Excel and select cell C4.
- Similarly, in the Home tab, click on the drop-down arrow of the Paste > Paste Special option from the clipboard group.
- As a result, the Paste Special dialog box will appear.
- Afterward, choose the Text option and click OK.
- You will get the dates instead of Excel-generated random numbers.
Hence, we can say that our formula works effectively, and we are able to fix the problem of Excel changing dates to random numbers.
Read More: How to Stop Autocorrect in Excel for Dates
Solution 3: Modify Formula Auditing Option
In our last solution process, we will modify the Formula Auditing option from the Formula tab to freeze the Excel changing dates to random numbers. In our dataset, the dates are displayed as random numbers in column D.
The steps of this process are shown below:
- First, go to the Formula tab from the Excel built-in ribbon.
- Now, in the Formula Auditing group, click on the fx Show Formula option to deselect it.
- You will notice that within a second, the random numbers will change into the dates.
Finally, we can say that our formula works successfully, and we can fix the problem of Excel changing dates to random numbers.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
That’s the end of this article. I hope that this article will be helpful for you and you will be able to fix Excel dates changing to random numbers issue. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
- Why Excel Is Changing My Numbers to Decimals
- How to Stop Excel from Changing Numbers to Scientific Notation
- How to Stop Excel from Auto Formatting Hyperlinks
- How to Stop Excel from Changing Numbers to Dates
- How to Prevent Cell Format Changes Automatically in Excel
- [Fixed!] Why Is Excel Changing My Numbers?
- How to Stop Excel from Changing Last Number to 0
- Why Is Excel Changing My Numbers to Zero?