Sometimes, when we input dates or do some operation with the dates, they change into some 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 to know the solutions, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
3 Possible Solutions to Stop Excel from Changing Dates to Random Numbers
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 us 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.
📚 Note:
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:
📌 Steps:
- 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.
Read More: How to Stop Excel from Auto Formatting Numbers (3 Easy Ways)
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:
📌 Steps:
- 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 (3 Quick Ways)
Similar Readings
- [Fixed!] Why Is Excel Changing My Numbers? (4 Reasons)
- How to Stop Excel from Changing Last Number to 0 (4 Easy Ways)
- Why Is Excel Changing My Numbers to Zero? (4 Solutions)
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 display as random numbers in column D.
The steps of this process are shown below:
📌 Steps:
- 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.
Read More: How to Stop Excel from Rounding 16 Digit Numbers (2 Easy Ways)
Conclusion
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.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!