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.
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.
- Select the cell containing the number
- Click the Number Format list arrow on the Home tab
- Select either the Short or Long Date format
The Format Cells dialogue box can be used for a more expansive list of formats.