HOUR Function (LibreOffice Calc)
The HOUR function extracts the hour component (0–23) from a time or datetime value. It is essential for time analysis, scheduling, and cleaning imported timestamp data.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the HOUR Function Does ▾
- Extracts the hour from a time or datetime
- Accepts serial numbers, real times, or converted text
- Returns an integer from 0 to 23
- Ignores minutes and seconds
It is designed to be simple, reliable, and ideal for time‑component extraction.
Syntax ▾
HOUR(time_value)
Arguments
- time_value:
A time, datetime, or serial number representing a time.
Basic Examples ▾
Extract hour from a time
=HOUR("14:30")
Returns 14.
Extract hour from a datetime
=HOUR("2024-03-15 08:45")
Returns 8.
Extract hour from a cell
=HOUR(A1)
Extract hour from NOW()
=HOUR(NOW())
Advanced Examples ▾
Extract hour from text using TIMEVALUE
=HOUR(TIMEVALUE(A1))
Extract hour from a timestamp with seconds
=HOUR("18:22:59")
Extract hour from a serial number
=HOUR(0.75)
0.75 days = 18:00 → returns 18.
Extract hour from imported CSV timestamps
=HOUR(DATEVALUE(LEFT(A1;10)) + TIMEVALUE(MID(A1;12;8)))
Extract hour from ISO datetime
=HOUR(TIMEVALUE(MID(A1;12;8)))
Extract hour from Excel serial dates imported as text
=HOUR(DATE(1899;12;30)+VALUE(A1))
Determine if a time is AM or PM
=IF(HOUR(A1) < 12; "AM"; "PM")
Bucket times into 3‑hour intervals
=INT(HOUR(A1)/3)*3
Check if a time is within business hours
=AND(HOUR(A1)>=9; HOUR(A1)<17)
Edge Cases and Behavior Details ▾
HOUR returns an integer (0–23)
Accepts:
- Real times
- Datetimes
- Serial numbers
- TIMEVALUE outputs
Invalid text → Err:502
HOUR ignores date components
=HOUR("2024-03-15 22:10") → 22
HOUR ignores minutes and seconds
HOUR of a number > 1
=HOUR(1.5) → 12
(1.5 days = 36 hours → 12:00 on day 2)
HOUR of negative values → Err:502
HOUR of an error → error propagates
Common Errors and Fixes ▾
Err:502 — Invalid argument
Cause:
- Text not recognized as a time
- Negative serial numbers
- Non-numeric values
Fix:
- Wrap with TIMEVALUE
- Clean text with TRIM or SUBSTITUTE
Wrong result due to date formatting
Fix:
- Ensure the cell contains a real datetime, not text
Unexpected hour from serial numbers
Cause:
- Serial numbers represent days, not hours
Fix:
- Use MOD(A1;1) to isolate time portion
Best Practices ▾
- Use HOUR for all hour‑extraction tasks
- Use TIMEVALUE to normalize text times
- Use INT or MOD to isolate time components
- Use HOUR with NOW() for dynamic dashboards
- Use HOUR with logical tests for scheduling rules
HOUR is your time‑component extractor — perfect for dashboards, scheduling logic, time analysis, and cleaning messy timestamp data.
Related Patterns and Alternatives ▾
- Use MINUTE and SECOND for other components
- Use TIME to construct times
- Use TIMEVALUE to convert text
- Use NOW for current datetime
- Use DATE for date construction
By mastering HOUR and its companion functions, you can build powerful, reliable, and fully dynamic time‑based workflows in LibreOffice Calc.