Convert Text to Date in LibreOffice Calc

LibreOffice Calc stores dates internally as serial numbers representing the number of days elapsed since an epoch date (by default, December 30, 1899). When dates are imported or entered as text strings, they cannot be used directly in arithmetic calculations or sorted chronologically without conversion. This article outlines the primary functions used to parse text strings into serial date numbers in LibreOffice Calc.

DATEVALUE Function

The DATEVALUE function is the primary and most direct tool for converting a date string into a serial number. It evaluates a text representation of a date and returns its internal integer value.

• Syntax: DATEVALUE("Text")
• Example: =DATEVALUE("2023-12-25") returns 45285.
• Usage Notes: The string must match an accepted date format defined in your LibreOffice locale settings (such as YYYY-MM-DD or MM/DD/YYYY). If the year is omitted, such as in "12-25", Calc automatically uses the current calendar year.

VALUE Function

The VALUE function is a general-purpose text-to-number conversion tool that also parses dates.

• Syntax: VALUE("Text")
• Example: =VALUE("2023-12-25") returns 45285.
• Usage Notes: While VALUE functions similarly to DATEVALUE when given valid date formats, it can also parse times, percentages, and standard numbers.

Parsing Non-Standard Dates Using DATE with Text Functions

If a text string uses an irregular format that DATEVALUE fails to recognize (for example, continuous digits like "20231225"), you can combine the DATE function with text manipulation functions (LEFT, MID, RIGHT).

• Syntax: DATE(Year, Month, Day)
• Example: To parse "20231225" in cell A1: =DATE(LEFT(A1, 4), MID(A1, 5, 2), RIGHT(A1, 2))
• Result: Returns the serial date number 45285.

Formatting the Output

Once a function generates the serial date number, the cell may initially display as an unformatted integer. To display it as a standard date, select the cell, open Format > Cells (or press Ctrl + 1), choose the Date category, and select your preferred display format.