DATEVALUE function

Userlevel 5

+8

Description

Converts a given Text to a Date using a specified format.

DATEVALUE(Date Text, Date Format)

Argument	Type	Dimensions	Description
Date Text (required)	Text	Any Dimensions	Text representing a date.
Date Format (optional)	Text	No Dimension	Specific format to transform the Text into a Date.

Type	Dimensions
Integer	Dimensions of argument Date Text

Case	Result
`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 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