Browse

TIMEVALUE Function (LibreOffice Calc)

Date & Time Beginner LibreOffice Calc LibreOffice 3.0

The TIMEVALUE function converts a text string representing a time into a real time value (a fractional day). It is essential for cleaning imported data, parsing timestamps, and enabling time arithmetic.

Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked

The **TIMEVALUE** function converts a **text string** representing a time into a real **time value** (a fractional day). It is essential for cleaning imported data, parsing timestamps, converting logs, and enabling time arithmetic on values that would otherwise be treated as plain text.

What the TIMEVALUE Function Does

  • 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.

Syntax

TIMEVALUE(text)

Arguments

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

Basic Examples

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.

Advanced Examples

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"))

Supported Formats

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)

Edge Cases and Behavior Details

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.

Common Errors and Fixes

Err:502 — Invalid argument

Cause:

  • Text not recognized as a time
  • Wrong separators
  • Extra characters

Fix:

  • Clean text with SUBSTITUTE
  • Use MID/LEFT/RIGHT to isolate time
  • Use VALUE to convert numeric text

Time displays as a number (e.g., 0.75)

Fix:

  • Apply a time format

Incorrect parsing due to locale

Fix:

  • Replace locale separators with “:”

Best Practices

  • 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
TIMEVALUE is your text‑to‑time conversion engine — perfect for cleaning messy imports, parsing logs, and turning timestamps into real, calculable time values.
  • 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.