How to Convert Text Dates to Dates in Excel?

How to Convert Text Dates to Dates in Excel? Sometimes, when you copy or input dates from other data sources into Excel, those dates may be formatted and stored as text. Here, I’ll introduce a trick to convert these text dates into standard dates in Excel.

How to Convert Text Dates to Dates in Excel?

Convert Text Dates with 2-Digit Years to Dates using Error Checking:

If the year in the text date is represented as two digits, like 01/07/14, you can apply the Error Checking utility to quickly convert text dates into standard dates.

Activate the worksheet containing the text dates and click on Formulas > Error Checking. See screenshot:

In the popped-out dialog, you can specify to convert years into either 19XX or 20XX. See screenshot:

Then, repeat clicking Convert XX to 19XX or Convert XX to 20XX to convert 2-digit year text dates to standard dates one by one until the dialog pops out to tell you that all error-checking is complete. See screenshot:

Now all the 2-digit year text dates have been converted into standard dates.

Convert Multiple Text Dates to Dates using a Formula:

For the feature of Error Checking, it can only convert 2-digit year text dates into standard dates, for other text dates, you can apply the formula =DATEVALUE().

Select a blank cell next to the text date and enter this formula =DATEVALUE(E1) (where E1 is the text date) and press Enter to get a 5-digit number, then drag the AutoFill handle of this cell down to fill the range you need.

Then, with the number strings retained, press Ctrl + 1 to open the Format Cells dialog and click Date in the Category list, then choose the date format you need from the right section. See screenshot:

Click OK, now the text dates have been converted to dates.