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: 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: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
