What DATEVALUE does
DATEVALUE turns text that Excel recognizes as a date into a numeric date serial. That number can be sorted chronologically or used in date calculations. Apply a Date format to display the result as a calendar date.
Use DATEVALUE for imported text dates. If the year, month, and day are separate numbers, the DATE function can build the date directly. For the conversion and formatting steps, see how to convert text to a date in Excel.
Practical examples
The serial numbers below use the 1900 date system. The second example assumes a regional date setting that recognizes month/day/year text.
Convert a date written directly in a formula
Enter the date text in double quotation marks:
=DATEVALUE("1/1/2008")
The result is 39448, representing January 1, 2008. A General number format shows 39448; a Date format displays the date. This input and result are documented in Microsoft's DATEVALUE reference.
Convert an imported date from a cell
Suppose A2 contains the text 8/22/2011. To reproduce a text entry manually, type '8/22/2011; the leading apostrophe marks the entry as text and is not displayed in the cell.
In B2, enter:
=DATEVALUE(A2)
The result is 40777, representing August 22, 2011. Format B2 as a date, then fill the formula down to convert other text dates in column A. The serial matches Microsoft's example for the same date text.
Common mistakes and notes
Regional settings affect interpretation
DATEVALUE depends on the date formats Excel recognizes under your system settings. Text such as 03/04/2026 can mean March 4 or April 3. Unrecognized date text returns #VALUE!; a successful conversion can still represent the wrong intended date, so check the source's date order.
Include the year when the result must stay fixed
If the text omits its year, DATEVALUE uses the current year from the computer's clock. Include a four-digit year for repeatable results. Time information in recognized date text is ignored, so DATEVALUE does not preserve a timestamp's time of day.
Formatting text does not convert it
Applying a Date format to the original text cell leaves it as text. Convert it with DATEVALUE first, then format the numeric result. If the result displays a serial number, the conversion has worked; change its number format to show a date.
Check the workbook's date system
The same date has serial numbers 1,462 apart in the 1900 and 1904 systems. For example, January 1, 2008 is 39448 in the 1900 system and 37986 in the 1904 system. Check the workbook setting when comparing serials or moving dates between files; see Microsoft's date-systems guide.
Under the 1900 system, DATEVALUE accepts dates from January 1, 1900 through December 31, 9999. Date text outside that range returns #VALUE!.