What is the Excel DATEVALUE Function?
The DATEVALUE function converts a text representation of a date into a proper excel date (a serial number recognized as a date by Excel). Proper excel DATE is more useful than a date represented as text when you want to manipulate data, like sorting, filtering or summaries and perform the date-based analysis.
DATEVALUE Syntax
DATEVALUE (date_text)
date_text, mandatory, a text represents a date format, could be written directly in an argument or stored in a cell.
Usage Notes:
- Return error #VALUE
- If the date_text argument falls outside of January 1, 1900, and December 31, 9999 range
- If the date_text argument contains no text in date format
- If you don’t write the year part of the date_text argument, then Excel will use the year based on the current date on the computer.
How to Use DATEVALUE Function in Excel
For example, there are data such as the image below. The first data until the sixth data is a TEXT while the seventh data is a DATE. What is the result of each DATEVALUE function in column C?
DATEVALUE Function #1
12/1/2017 is a text with “mm/dd/yyyy” date format for December 1, 2017. The result is 43070.
DATEVALUE Function #2
Dec 1, 2017, same as the first data but different format. The result is 43070.
DATEVALUE Function #3
01-Dec-2017 same as the two previous examples, the same date but different formats. The result is 43070.
DATEVALUE Function #4
01-Dec, there are only date and month, no year information, Excel will use the current year on the computer (the current year is 2018). The result is 43435
DATEVALUE Function #5
01-Dec-1890 is a text with “mm-Mon-yyyy” date format for December 1, 1890. The result is error #VALUE. Why? Because 01-Dec-1890 is outside range from January 1, 1900, until December 31, 9999
DATEVALUE Function #6
“date” is a text without a date format. The result is error #VALUE. The date_text argument must contain text and has a date format than Excel will convert it to a DATE.
DATEVALUE Function #7
12/1/2018 is a DATE (by default right aligned) no need to be converted by the DATEVALUE function, if forced to convert will even generate an error.
The all results are as shown below
