w3resource

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:

Name Description Data Type Required/
Optional
Date_text Text that represents a date in an Excel date format, or a reference to a cell that contains text that represents a date in an Excel date format. Text Required

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.

datevalue

Example - 2:

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

date2

Example - 3:

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

datevalue3

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.

datevalue4

Example - 5:

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

datevalue5

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



Follow us on Facebook and Twitter for latest update.