DATEVALUE Function

Sometimes, dates in Excel appear as text values that are not recognized as proper dates. The DATEVALUE function is meant to convert a date represented as a text string into a valid Excel date. Proper Excel dates are more useful than text dates since they can be formatted as a date, and directly manipulated with other formulas.

The DATEVALUE function takes just one argument, called date_text. If date_text is a cell address, the value of the cell must be text. If date_text is entered directly into the formula it must be enclosed in quotes.

Examples

To illustrate how the DATEVALUE function works, the formula below shows how the text "3/10/1975" is converted to the date serial number 27463 by DATEVALUE:

=DATEVALUE("3/10/1975")  // returns 27463

Note that DATEVALUE returns a serial number, 27463, which represents March 10, 1975 in Excel's date system. A date number format must be applied to display this number as a date.

In the example shown, column B contains dates entered as text values, except for B15, which contains a valid date. The formula in C5, copied down, is:

=DATEVALUE(B5)

Column C shows the number returned by DATEVALUE, and column D shows the same number formatted as a date. Notice that Excel makes certain assumptions about missing day and year values. Missing days become the number 1, and the current year is used if there is no year value available.

Alternative formula

Notice that the DATEVALUE formula in C15 fails with a #VALUE! error, because cell B15 already contains a valid date. This is a limitation of the DATEVALUE function. If you have a mix of valid and invalid dates, you can try the simple formula below as an alternative:

=A1+0

The math operation of adding zero will cause Excel will try to coerce the value in A1 to a number. If Excel can parse the text into a proper date it will return a valid date serial number. If the date is already a valid Excel date (i.e. a serial number), adding zero will have no effect, and generate no error.

Notes

• DATEVALUE will return a #VALUE error if date_text refers does not contain a date formatted as text.