Excel DATEVALUE() function

DATEVALUE() function

The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. The function is helpful in such cases where a worksheet contains dates in a text format that you want to filter, sort, or format as dates, or use in date calculations.

To view a date serial number as a date, you must apply a date format to the cell if not formatted.

Version: Excel 2013

Syntax:

DATEVALUE(date_text)

Parameters:

The DATEVALUE function returns the #VALUE! error value if the value of the date_text argument falls outside of this range that is a date between January 1, 1900 and December 31, 9999.

If the year portion of the date_text argument is omitted, the DATEVALUE function uses the current year.

Remarks

• Excel stores date as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2010, is serial number 40179 because it is 40178 days after January 1, 1900.

Example - 1:

The example below shows that the serial number of a date entered as text.

Example - 2:

The example below shows that the serial number of a date entered as text.

Example - 3:

The example below shows that the serial number of a date entered as text.

Example - 4:

Serial number of a date entered as text, using the 1900 date system, and assuming the computer's built-in clock returns 2015 as the current year.

Example - 5:

Serial number of a date created by combining the values in cells A2, A3, and A4.

Previous: Excel BETA.DIST() function
Next: Excel DAY() function