Convert Text into a Date Format

If you import dates into Excel from another system, or from the web, they may appear in Excel using a text format. This looks great but will not provide full functionality. Unless you can convert the text into a date format you will not be able to use that data in formulas, PivotTables and other Excel features.

Fortunately, Excel provides a function that can convert text into a date format, and it is very easy to use.

Using the DATEVALUE Function

The DATEVALUE function is used to convert a date in the form of text to a number that represents the date. The function only requires one argument as shown below.

=DATEVALUE(date_text)

Date_text is the location of the text that you want to convert into a number. This is commonly entered as a cell reference such as =DATEVALUE(B2), where B2 holds the date represented by a text format.

Format the Number as a Date

After using the function to turn it into a number format. It then needs to be formatted as a date.

1. Select the cell containing the number
2. Click the Number Format list arrow on the Home tab
3. Select either the Short or Long Date format

The Format Cells dialogue box can be used for a more expansive list of formats.