TIME Function (LibreOffice Calc)
The TIME function constructs a valid time value from hour, minute, and second components. It automatically normalizes overflow values and is essential for building timestamps, cleaning data, and performing time arithmetic.
What the TIME Function Does
- Builds a valid time from hour, minute, second
- Automatically normalizes overflow values
- Returns a fractional day (serial number)
- Works with any numeric inputs
- Ideal for constructing timestamps and cleaning data
It is designed to be robust, predictable, and essential for time‑based workflows.
Syntax
TIME(hour; minute; second)
Arguments
-
hour:
Can be any integer (positive or negative). -
minute:
Can overflow (e.g., 90 → +1 hour 30 minutes). -
second:
Can overflow (e.g., 75 → +1 minute 15 seconds).
Basic Examples
Construct 2:30 PM
=TIME(14; 30; 0)
Construct 8:45:12 AM
=TIME(8; 45; 12)
Midnight
=TIME(0; 0; 0)
Noon
=TIME(12; 0; 0)
Overflow Normalization Examples
75 minutes → 1 hour 15 minutes
=TIME(10; 75; 0)
Returns 11:15.
90 seconds → 1 minute 30 seconds
=TIME(9; 0; 90)
Returns 09:01:30.
Negative minutes
=TIME(10; -30; 0)
Returns 09:30.
Negative seconds
=TIME(10; 0; -30)
Returns 09:59:30.
Large hour values
=TIME(30; 0; 0)
Returns 06:00 (wraps after 24 hours).
Advanced Examples
Build a timestamp from components
=DATE(A1; B1; C1) + TIME(D1; E1; F1)
Add hours to a timestamp
=A1 + TIME(B1; 0; 0)
Add minutes to a timestamp
=A1 + TIME(0; B1; 0)
Add seconds to a timestamp
=A1 + TIME(0; 0; B1)
Convert H:M:S text to time
=TIME(VALUE(LEFT(A1;2)); VALUE(MID(A1;4;2)); VALUE(RIGHT(A1;2)))
Convert seconds to time
=TIME(0; 0; A1)
Convert minutes to time
=TIME(0; A1; 0)
Convert hours to time
=TIME(A1; 0; 0)
Build a time from decimal hours
=TIME(INT(A1); INT((A1-INT(A1))*60); ((A1*3600) MOD 60))
Build a time from milliseconds
=TIME(0; 0; A1/1000)
Combine date and time from separate cells
=A1 + TIME(HOUR(B1); MINUTE(B1); SECOND(B1))
Edge Cases and Behavior Details
TIME returns a fractional day (0–1)
Accepts:
- Any numeric values
- Negative values
- Overflow values
Invalid text → Err:502
TIME wraps hours beyond 24
TIME normalizes minutes and seconds
TIME ignores non-integer decimals in arguments
(Decimals are truncated)
TIME of an error → error propagates
Common Errors and Fixes
Err:502 — Invalid argument
Cause:
- Text instead of numbers
- Non-numeric characters
Fix:
- Wrap with VALUE
- Clean text with TRIM or SUBSTITUTE
Unexpected time due to overflow
Fix:
- Check hour/minute/second inputs
- Use MOD for explicit control
Time displays as a number (e.g., 0.75)
Fix:
- Apply a time format
Best Practices
- Use TIME to construct clean, normalized time values
- Use TIMEVALUE to convert text times
- Use TIME with DATE to build full timestamps
- Use TIME for adding hours/minutes/seconds
- Use TIME with VALUE to clean imported data
- Use TIME for converting raw seconds/minutes to time
Related Patterns and Alternatives
- Use TIMEVALUE to convert text to time
- Use HOUR, MINUTE, SECOND for extraction
- Use NOW for dynamic date+time
- Use DATE for constructing dates
- Use VALUE to convert numeric text
By mastering TIME and its companion functions, you can build powerful, reliable, and fully normalized time‑based workflows in LibreOffice Calc.
Reproducible example (tested in LibreOffice Calc)
This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.
=HOUR(TIME(14;30;0))
Expected result: 14.
Create a time from separate hour and minute values.
If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.