Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

DATEVALUE

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

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.

Keep Reading Similar Functions:

Return date by combining Hour, Minute and Seconds in Excel

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

Read More »
Converts a decimal number into time in Excel

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

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

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

Read More »