Convert text date into a serial value in Excel
Converts a date in the form of text to a serial number


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.

Return Value

Returns a date serial number.

Key Notes

  • 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.

Keep Reading Similar Functions:

Get a date by entering the year, month, and day in Excel


The DATE function returns a date value using year, month and date, sequentially. I.e.; to merge all 3 date component, =DATE(2012,12,4) returns “04-12-2012”.

Read More »
Return date by combining Hour, Minute and Seconds in Excel


The TIME function returns the time value by merging the three-time components. Example, use =TIME(9,15,35), that may returns “09:15:35”.

Read More »
Converts a decimal number into time in Excel


The TIMEVALUE function returns the time serial number (in decimal). I.e., =TIMEVALUE(“12:45:30 PM”) as 0.53125 (proportion of total time value of a day).

Read More »