Share how to fix dates being converted to numbers in Excel

The error of dates being converted to numbers in Excel makes you frustrated because you don't know how to handle it? TipsMake has revealed details on how to fix this Excel error in the article. Let's Go!

Dates in Excel are definitely data you regularly update while working with this tool. Normally, formatting dd/mm/yyyy in Excel is not too difficult. But sometimes users still encounter the error of dates being converted to numbers in Excel . Don't worry too much because TipsMake will have a way to help you fix this error.

Share how to fix dates being converted to numbers in Excel Picture 1

Step-by-step instructions on how to fix dates being converted to numbers in Excel

Dates are converted to numbers in Excel, sometimes converted to Text and Excel does not recognize the time value. This is a very familiar case in the user's work process. To fix it, you need to do the following steps:

1/ Set up time display rules for the computer

If you have never set up the time display, or you just bought a new computer, the device will display by default Month/Day/Year/. Therefore, you need to change it back to Day/Month/Year with the following steps:

1. Step 1 : The user clicks on the magnifying glass search icon on the taskbar > Type search and open Control Panel > Then, click on Region (Region and Language).
2. Step 2 : At the Region table > Click on the Additional Settings. button.

Share how to fix dates being converted to numbers in Excel Picture 3

1. Step 3 : In the new window, click on the Date tab > At the Short Date line > Click on the arrow and select dd/MM/yyyy > Click OK to save.

Share how to fix dates being converted to numbers in Excel Picture 5

2/ Fix date format errors in Excel

After setting up date display rules for your computer, the next step is to fix the error of not being able to format dates in Excel.

Format for time data field in Excel

The user highlights the column displaying the error date in your Excel File > Press Ctrl + 1 to quickly open the Format Cells table > Click the Number tab > Then the user clicks on Date or the Custom item > In the Type box You enter dd/mm/yyyy > Click OK to complete this process.

Share how to fix dates being converted to numbers in Excel Picture 7

Filter and correct incorrect date, month, year data

To filter data and search for incorrect date formats, in the open Excel file > Highlight the entire data table > Then select the Data tab > Select Filter > Click the filter arrow button in the time column .

Share how to fix dates being converted to numbers in Excel Picture 9

Some data is not the same as other data (for example, the data in the image is sorted by year, but some data is shown specifically), the data shown in particular is in the wrong date format month. Because the new time format standard data is automatically sorted.

After you know which data cell is wrong, uncheck Select All > Select only lines with time errors to find the location > After finding it, correct it to the correct time format.

Share how to fix dates being converted to numbers in Excel Picture 11

Above are the steps to fix the error of dates being converted to numbers in Excel. Hope this guide will help you fix the error successfully! TipsMake has many other articles about Excel, refer to it now to make your experience smoother!

Isabella Humphrey

Update 09 December 2023