DATEVALUE Function (LibreOffice Calc)

• Converts text → real date value
• Enables date arithmetic on imported or inconsistent data
• Supports many locale-aware date formats
• Returns a serial date number (formatted as a date)

It is designed to be robust, flexible, and ideal for data cleaning and normalization.

DATEVALUE(text)

Arguments

• text:
  A string representing a date in a recognizable format.
  Must match your locale’s date interpretation rules unless ISO format is used.

Convert a simple date string

=DATEVALUE("2024-05-10")

Returns 2024‑05‑10.

Convert a locale-formatted date

=DATEVALUE("05/10/2024")

Interpretation depends on locale (MDY vs DMY).

Convert a month name

=DATEVALUE("March 15, 2024")

Convert ISO format (always safe)

=DATEVALUE("2024-03-15")

ISO 8601 is universally recognized.

Convert text from a cell

=DATEVALUE(A1)

Normalize imported CSV dates

=DATEVALUE(TRIM(A1))

Convert dates with extra text

=DATEVALUE(REGEX(A1; "[0-9\-\/]+"; 0))

Convert “YYYYMMDD” numeric text

=DATEVALUE(LEFT(A1;4) & "-" & MID(A1;5;2) & "-" & RIGHT(A1;2))

Convert “DD.MM.YYYY”

=DATEVALUE(SUBSTITUTE(A1; "."; "-"))

Convert “Month DD YYYY”

=DATEVALUE(TEXT(A1; "MMMM DD YYYY"))

Convert text with weekday included

=DATEVALUE(MID(A1; FIND(" ";A1)+1; 99))

Example: “Tue, 12 Mar 2024”

Convert Excel serial date stored as text

=DATEVALUE("1899-12-30") + VALUE(A1)

Convert ambiguous formats safely

=DATEVALUE(TEXT(DATE(VALUE(RIGHT(A1;4)); VALUE(MID(A1;4;2)); VALUE(LEFT(A1;2))); "YYYY-MM-DD"))

Convert text with ordinal suffixes (“1st”, “2nd”, “3rd”)

=DATEVALUE(REGEX(A1; "([0-9]+)(st|nd|rd|th)"; "$1"))

DATEVALUE recognizes:

• ISO: YYYY-MM-DD
• Locale formats: MM/DD/YYYY, DD/MM/YYYY, etc.
• Month names: “March 5 2024”, “5 March 2024”
• Abbreviated months: “Mar 5 2024”
• Mixed punctuation: “2024.03.05”, “2024/03/05”
• Weekday prefixes: “Tue 5 Mar 2024”

DATEVALUE returns a number, not text

=TYPE(DATEVALUE("2024-01-01")) → 1 (number)

Locale matters

“05/10/2024” may mean May 10 or October 5 depending on locale.

Invalid text → Err:502

Examples:

• “2024-13-40”
• “banana”
• “2024/??/10”

Leading/trailing spaces are ignored

=DATEVALUE(" 2024-01-01 ")

DATEVALUE does not parse time

Use TIMEVALUE or VALUE for datetime strings.

DATEVALUE of an error → error propagates

DATEVALUE handles leap years correctly

=DATEVALUE("2024-02-29") → valid  
=DATEVALUE("2023-02-29") → Err:502  

• Use ISO format (YYYY-MM-DD) whenever possible
• Clean imported data with TRIM, SUBSTITUTE, or REGEX
• Use DATEVALUE + VALUE for Excel serial text
• Use DATEVALUE to normalize inconsistent formats
• Use DATEVALUE before YEAR/MONTH/DAY extraction

• Use DATE to construct dates from components
• Use TIMEVALUE for time-only text
• Use VALUE for numeric serials
• Use TEXT to reformat dates
• Use YEAR, MONTH, DAY to extract components
• Use EDATE and EOMONTH for month offsets

By mastering DATEVALUE and its companion functions, you can build powerful, reliable, and fully normalized date workflows in LibreOffice Calc.