Syntax. DATEVALUE(date_text)

Definition. This function converts a date formatted as text into a serial number based on the Excel date system.

Arguments

Background. If a date is formatted as text (for example, if it was imported in this format), use the DATEVALUE() function to convert the date into a serial number so that you can use the result for other calculations.

Most functions convert dates formatted as text automatically into serial numbers, but if you use imported data or worksheet functions from a third-party add-in, this might not always be true. To ensure that a date is converted, use the DATEVALUE() function.

For example, “12/12/2005” and “9/9/2001” are strings in quotation marks. If you use the standard Excel for Windows date system, the date string has to be a date from January 1, 1900, through December 31, 9999. If you use the standard Excel for Mac date system, the date string must be a date from January 1, 1904, through December 31, 9999. If the date string is a date out of range, the DATEVALUE() function returns the #VALUE! error.

If a date string doesn’t include the year, the DATEVALUE() function uses the year from the system time of your computer. The time in the date string is ignored.

Example. After the values are imported, they appear as text in a date column. In order for these dates to be usable in other calculations, the values have to be converted into numeric date values. The formula

=DATEVALUE("12/12/2008")

returns the date value 12/12/2008. This value is a serial date number in the Excel date system. Here are some more examples: