Converting Text Dates to a Numeric Dates

Converting Text Dates to Numeric Dates

Sometimes when you import data, particularly dates,  Excel treats the data as text. You can use the DATEVALUE() function to convert text dates to real numeric dates.
 DATEVALUE() returns the serial number of the selected date. Once you have the serial date, you can format it to display as a date.

In this example, the data in Column A are dates that Excel treats as text. (A good way to tell if Excel is treating something as a number or text is to check the alignment. Text is left aligned whereas numbers are left aligned.)  In column B2, I used the formula =DATEVALUE(A2) to change the text in cell A2 into a serial number which displays as 39894.

39894 is the serial number of the date in cell A2.



To convert it to something meaningful, you just need to go the Number group  on the Home Tab

and click on the drop-down arrow beside General



In this particular example, I then selected the short date

The result is shown below:

DATEVALUE() only works on text dates. If you try it on a column of text and numeric dates, you will get a #VALUE error on the entries that are numeric.