Convert Text Dates into Real Dates in LibreOffice Calc

In LibreOffice Calc, a real spreadsheet date is a numeric value with a date display format. Text that merely looks like a date may not behave correctly in sorting, subtraction or COUNTIFS conditions. The safest conversions use unambiguous ISO-format text such as 2026-10-01.

First check whether conversion is needed

If A2 displays 2026-10-01, enter in another cell:

=ISNUMBER(A2)

TRUE indicates the underlying cell is numeric, as genuine Calc dates normally are. FALSE means it contains something else, often text. This type check does not establish whether a numeric value represents the right date; inspect the formatted date as well.

Convert a text date with DATEVALUE

Make sure A2 contains the text 2026-10-01. Enter in B2:

=DATEVALUE(A2)

B2 now holds a date serial number. Choose Format → Cells → Numbers → Date to display it as a calendar date. To verify the parsed year independently:

=YEAR(DATEVALUE(A2))

Expected result: 2026. This deliberately uses the ISO year-month-day order.

Avoid ambiguous input

03/04/2026 could mean March 4 or April 3, depending on locale. If a system exports day-first or month-first dates, use a matching Date (DMY) or Date (MDY) column type in the CSV Text Import dialog. For a portable formula, use =DATE(2026;10;1) to create a known date rather than comparing ambiguous text.

Use converted dates in calculations

Once B2:B100 contains genuine Calc dates, you can count records in October 2026 with:

=COUNTIFS(B2:B100;">="&DATE(2026;10;1);B2:B100;"<"&DATE(2026;11;1))

Using the next month’s first day as an exclusive upper bound also includes dates with times on October 31. See the COUNTIFS date range tutorial.

Troubleshooting

• #VALUE! from DATEVALUE: the text may be invalid, ambiguous or incompatible with locale settings.
• Result appears as a large integer: select a date number format; the serial number is expected.
• Original code was unintentionally converted to a date: reimport it as text instead. See prevent CSV date conversion.

Common questions

Should I apply DATEVALUE to cells that are already dates? Usually not. Work with the numeric date values directly.

Why doesn’t changing the cell format fix a text date? Formatting changes its appearance; it may not change its underlying type. Convert explicitly and verify.

Official reference: LibreOffice Help. This tutorial focuses on LibreOffice Calc; menu names and behavior can vary by version, operating system and locale.