DATEVALUE function

  • 21 December 2021
  • 0 replies

Userlevel 4
Badge +2



Converts a date stored as text into a Date typed Value.



DATEVALUE(date_text, date_format)


Return type




Case Results
DATEVALUE("2021-12-24", "yyyy-MM-dd") 2021-12-24
DATEVALUE("2021-2-24", "yyyy-M-dd") 2021-02-24
DATEVALUE("2021/12/24", "yyyy/MM/dd") 2021-12-24
DATEVALUE("2021 Dec 24", "yyyy MMM dd") 2021-12-24
DATEVALUE("2021 December 24", "yyyy MMMM dd") 2021-12-24
DATEVALUE("21 12 24", "yy MM dd") 2021-12-24
DATEVALUE("21-02", "yy-MM") 2021-02-01
DATEVALUE("21-2", "yy-M") 2021-02-01


Note: Formats using abbreviated name of the month or full name of the month only support English.


Date format 

date_format should be a constant string with at least one placeholder for Year, Month or Day, other characters must be surrounded by quotes.


Format specifier Description
"y" The year, from 0 to 99.
"yy" The year, from 00 to 99.
"yyy" The year, with a minimum of three digits.
"yyyy" The year as a four-digit number.
"yyyyy" The year as a five-digit number.



Format specifier Description
"M" The month, from 1 through 12.
"MM" The month, from 01 through 12.
"MMM" The abbreviated name of the month: "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
"MMMM" The full name of the month: "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"

Note: without any format specifier for the Month, the fallback is January.



Format specifier Description
"d" The day of the month, from 1 through 31.
"dd" The day of the month, from 01 through 31.

Note: without any format specifier for the Day, the fallback is the first day of the month.



Format specifier Description
'string' Literal string delimiter.



Excel equivalent: DATEVALUE

See also: DATE function

0 replies

Be the first to reply!