TIMEVALUE Function (LibreOffice Calc)

• Converts text into a real time value
• Accepts many common time formats
• Returns a fractional day (0–1)
• Enables arithmetic on imported timestamps
• Works with combined date+time strings (time portion only)

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

TIMEVALUE(text)

Arguments

• text:
  A string representing a time (e.g., “14:30”, “8:45 PM”, “18:22:59”).

Convert a simple time

=TIMEVALUE("14:30")

Convert a time with seconds

=TIMEVALUE("08:45:12")

Convert a 12‑hour time with AM/PM

=TIMEVALUE("7:15 PM")

Extract time from a datetime string

=TIMEVALUE("2024-03-15 08:45:12")

Returns 08:45:12 as a time value.

Extract time from ISO timestamp

=TIMEVALUE(MID(A1; 12; 8))

Extract time from CSV timestamp

=TIMEVALUE(MID(A1; 12; 8))

Convert Excel serial date stored as text

=TIMEVALUE(TEXT(DATE(1899;12;30)+VALUE(A1); "HH:MM:SS"))

Convert H:M:S text to time

=TIMEVALUE(A1)

Convert milliseconds to time (via text)

=TIMEVALUE(TEXT(A1/1000/86400; "HH:MM:SS"))

Normalize inconsistent time formats

=TIMEVALUE(SUBSTITUTE(A1; "."; ":"))

Convert “HHMMSS” text to time

=TIMEVALUE(LEFT(A1;2) & ":" & MID(A1;3;2) & ":" & RIGHT(A1;2))

Convert “HHMM” text to time

=TIMEVALUE(LEFT(A1;2) & ":" & RIGHT(A1;2))

Combine DATEVALUE + TIMEVALUE for full timestamp

=DATEVALUE(LEFT(A1;10)) + TIMEVALUE(MID(A1;12;8))

Convert decimal hours to time via text

=TIMEVALUE(TEXT(A1/24; "HH:MM:SS"))

TIMEVALUE accepts:

• HH:MM
• HH:MM:SS
• H:MM AM/PM
• HH:MM:SS AM/PM
• YYYY-MM-DD HH:MM:SS (time portion only)
• HH.MM (with cleanup)
• HHMMSS (with reconstruction)
• ISO timestamps (with MID extraction)

TIMEVALUE returns a fractional day (0–1)

TIMEVALUE ignores the date portion of a datetime

=TIMEVALUE("2024-03-15 22:10:33") → 22:10:33

Invalid text → Err:502

TIMEVALUE does not accept:

• Non‑time text
• Locale‑dependent formats without cleanup
• Negative times
• Times > 24 hours

TIMEVALUE of an error → error propagates

TIMEVALUE requires text

For numeric times, use TIME or arithmetic.

• Use TIMEVALUE to normalize imported time strings
• Use DATEVALUE + TIMEVALUE for full timestamps
• Clean text with SUBSTITUTE before conversion
• Use TIMEVALUE for logs, CSVs, and API exports
• Use TIMEVALUE with HOUR/MINUTE/SECOND for extraction
• Apply proper time formatting after conversion

• Use TIME to construct times from components
• Use DATEVALUE for date text
• Use NOW for dynamic date+time
• Use VALUE to convert numeric text
• Use HOUR, MINUTE, SECOND for extraction

By mastering TIMEVALUE and its companion functions, you can build powerful, reliable, and fully normalized time‑processing workflows in LibreOffice Calc.