How to Convert a Date from Text with Excel’s DATEVALUE Function

You may have data in your Excel worksheet that looks like a date but is not represented as an Excel date value. For example, if you enter 01-24-18 in a cell, Excel would have no way of knowing whether this is January 24, 2018, or the code for your combination lock. If it looks like a date, you can use the Excel DATEVALUE function to convert it to an Excel date value.

In practice, any standard date format entered into a cell is recognized by Excel as a date and converted accordingly. However, there may be cases, such as when text dates are imported from an external data source or data is copied and pasted into Excel, for which you need DATEVALUE.

Why not enter dates as text data? Although they may look fine, you can’t use them for any of Excel’s powerful date calculations without first converting them to date values.

Excel’s DATEVALUE function recognizes almost all commonly used ways that dates are written. Here are some ways that you may enter August 14, 2018:

• 8/14/18
• 14-Aug-2018
• 2018/08/14

DATEVALUE can convert these and several other date representations to a date serial number.

After you’ve converted the dates to a date serial number, you can use the dates in other date formulas or perform calculations with them.

To use Excel’s DATEVALUE function, follow these steps:

1. Select the cell where you want the date serial number located.
2. Type =DATEVALUE( to begin the function entry.
3. Click the cell that has the text format date.
4. Type a ) and press Enter.

   The result is a date serial number unless the cell where the result is displayed has already been set to a date format.

The following image shows how some nonstandard dates in column A have been converted to serial numbers with the Excel DATEVALUE function in column B. Then column C displays these serial numbers formatted as dates.

Do you notice something funny in the image above? Normally, you aren’t able to enter a value such as the one in cell A4 — 02-28-10 — without losing the leading 0. The cells in column A had been changed to the Text format. This format tells Excel to leave your entry as is. The Text format is one of the choices in the Category list in the Format Cells dialog box.

Note also that the text date in cell A8, Feb 9 14, could not be converted by DATEVALUE, so the function returns the error message #VALUE#. Excel is great at recognizing dates, but it’s not perfect! In cases such as this, you have to format the date another way so DATEVALUE can recognize it.