DATEVALUE

Rational

Convert text date into a serial value.

Description

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.

Syntax

=DATEVALUE(date_text)

Parameters

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.

TIMEVALUE

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

TIME

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

DATE

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