Convert text date into a serial value.
The Excel DATEVALUE function translates a static piece of string resembling as a date into a valid date serial number. Example; if date is in text format, use =DATEVALUE(“10-6-17”) that returns 42896 (10-06-2017).
The DATEVALUE recognizes almost all kind of date format which is not automatically converted into date format by Excel. This function is helpful in case the date is in a text format, and users wish to use them with another formula.
date_text – Input text string resembling a date.
Returns a date serial number.
- The DATEVALUE function only returns a date serial number if the argument is not in a date format but resemble like a date.
- Example: If a return value of the DATEVALUE function is ‘43261’ we can change the serial number by changing the cell format to date which will result ‘06-10-2018’ or one of the selected date format of the user.
- We can directly input the text as an argument in an enclosed double quotation mark.
- If the argument in the DATEVALUE function link to a specific cell; the format should have a value other than one of the recognized date or date serial number format in the Excel.
- The result will show #VALUE! error If the input argument is not recognized as text or if DATEVALUE formula is linked to a cell that does not look like a date. Example: If we enter 123 in the argument, it will return #VALUE! error as the input does not feature like a date.