HOUR function
HOUR extracts the hour from a time value and returns a number from 0 to 23. It is useful for grouping timestamps by hour of day, and it always uses the 24-hour clock.
Example
Read it as a sentence: pull out the hour from the date and time in F12, which gives 18.
Excel shows 18
F
1
2
'123
3
2024-02-29
4
TRUE
5
=1/0
6
Hello World
7
0
8
=""
9
abc
10
12.5
11
=NA()
12
46031.75
Data
Syntax
HOUR(serial_number)
-
serial_number(required) - A time, date-time, or fraction of a day.
Returns: The hour, 0 to 23.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=HOUR(0.75) | 18 | 18:00 |
=HOUR("6:30 PM") | 18 | Text time |
=HOUR(46031.5) | 12 | Datetime serial |
=HOUR(1.99999999) | 0 | Near-midnight rounding |
=HOUR("25:00") | 1 | Text over 24 hours |
=HOUR(Data!F12) | 18 | Datetime cell |
=HOUR(-0.5) | #NUM! | Negative |
=HOUR("abc") | #VALUE! | Bad text |
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
4
TRUE
5
=1/0
6
Hello World
7
0
8
=""
9
abc
10
12.5
11
=NA()
12
46031.75
Data
Watch out for
- Always 24-hour, even if the cell displays AM/PM.
- A time stored as text may need TIMEVALUE first.
Where it works
- Excel
- HOUR is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Date and time functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .