Description
The DATEVALUE function converts a text string representing a date into a date value.
Usage
DATEVALUE(text)
| Argument | Type | Required | Description |
|---|---|---|---|
| text | text | Yes | A text representation of a date |
Remarks
DATEVALUE recognizes a wide range of date formats commonly used in Latin-script languages. The day, month, and year components may be separated by spaces, hyphens (-), slashes (/), backslashes (\), commas, periods, or tabs.
The month may be given as a number from 1 to 12, or as a month name in any language that uses the Latin alphabet, including English, French, Spanish, Portuguese, Italian, Dutch, German, and Turkish. Both full names and common abbreviations are recognized (for example, March, Mar, Mar., marzo, mars, mart, ocak).
ActivityInfo supports dates between the year 1000 CE and 9999 CE. When a four-digit year is found in the input, it is used directly.
Two-digit years are interpreted using a pivot of 50:
- Years 0-49 are interpreted as 2000-2049. For example,
15 May 01is parsed as May 15, 2001. - Years 50-99 are interpreted as 1950-1999. For example,
30 May 51is parsed as May 30, 1951.
If the input cannot be parsed as a valid date, the function returns an error.
Accepted formats
The following inputs are all parsed correctly:
| Input | Parsed as |
|---|---|
| 2011-01-15 | 15 January 2011 |
| 30/4/2082 | 30 April 2082 |
| 30/4/07 | 30 April 2007 |
| 30/4/82 | 30 April 1982 |
| 11-01-15 | 11 January 2015 |
| 15 May 2049 | 15 May 2049 |
| May 15 85 | 15 May 1985 |
| Oct 31st, 1940 | 31 October 1940 |
| Wed, 14th of May, 1999 | 14 May 1999 |
| 3.12.2018 | 3 December 2018 |
| 3.ocak.2018 | 3 January 2018 |
| 15.mayıs.2018 | 15 May 2018 |
Examples
Parsing dates imported as text
When importing data from a CSV or another external source, date columns may arrive as text. Use DATEVALUE to convert them into a date value that can be used in calculations and validation rules:
DATEVALUE(IMPORTED_DATE)
Converting a fixed date string
DATEVALUE("2024-03-15")
returns the date value March 15, 2024.
Comparing a parsed date against today
If a form collects a date as free text and you need to flag values in the future, you can combine DATEVALUE with TODAY:
DATEVALUE(DATE_TEXT) > TODAY()