[Fixed!] Excel Changing Dates to Random Numbers (3 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.

📚 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.

Choosing proper cell format to fix Excel changing dates to random number

  • You will see all the random numbers in column D converted into original dates.

Modify Cell Format to Fix Excel Changing Dates to Random Number

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


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.

Copying data to stop Excel changing date into random numbers

  • 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.

Choosing Paste Special option to paste data in Word

  • As a result, a small dialog box titled the Paste Special will appear.
  • Select the Unformatted Text option and click OK.

Selecting proper format to fix Excel changing dates to random numberso paste data to fix

  • You will get the dates in your Word file.

Dates in 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.

Pasting Data by Paste Special in Excel to Stop Excel Changing Dates to Random Numbers

  • As a result, the Paste Special dialog box will appear.
  • Afterward, choose the Text option and click OK.

Select proper option to paste data in Excel

  • You will get the dates instead of Excel-generated random numbers.

Use Paste Special Feature to Fix Excel Changing Dates to 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:

📌 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.

Selecting fx Show Formulas option to fix dates changing problem

  • You will notice that within a second, the random numbers will change into the dates.

Modify Formula Auditing Option to Fix Excel Changing Dates to Random Numbers

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


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo