The Excel HOUR function pulls the hour out of any time as a plain number from 0 (midnight) to 23 (11 PM). Because Excel stores times as fractions of a 24-hour day — 0.5 is noon, 0.75 is 6:00 PM — you can’t just look at the stored number to get the hour; HOUR decodes it for you. It works on real times, full date-time stamps, and even text like "6:45 PM". Its siblings MINUTE and SECOND extract the other two components, and TIME puts them back together.
6/12/2026 2:30 PM the result is 14 — HOUR always speaks 24-hour clock, never AM/PM.
Syntax
| Argument | Description | |
|---|---|---|
serial_number | Required | The time to read: a cell with a time or date-time, a TIME/TIMEVALUE result, a raw day fraction like 0.75, or quoted text such as "6:45 PM". |
How Excel stores times: every time is a fraction of one day — 0.25 is 6:00 AM, 0.5 is noon, 0.75 is 6:00 PM. HOUR multiplies that fraction back into clock units and hands you the hour as an integer from 0 to 23. Any date part of the value is simply ignored. Available in every version of Excel.
Pull the hour from a timestamp
Column A holds order timestamps. HOUR, MINUTE, and SECOND take each one apart — note row 5: a bare day fraction works just as well as a formatted time:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Timestamp | =HOUR(A) | =MINUTE(A) | =SECOND(A) |
| 2 | 6/12/2026 8:26:05 AM | 8 | 26 | 5 |
| 3 | 6/12/2026 2:30:00 PM | 14 | 30 | 0 |
| 4 | 6/12/2026 11:59:59 PM | 23 | 59 | 59 |
| 5 | 0.75 | 18 | 0 | 0 |
HOUR is the standard tool for bucketing events by hour of day — pair it with TEXT for a readable label:
Try it: interactive HOUR demo
Pick any time and watch HOUR, MINUTE, and SECOND take it apart — plus the day fraction Excel stores behind the scenes.
Decimal hours and shift logic
Payroll and billing want 8.5 hours, not 8:30. Rebuild a decimal from the components:
For a duration (end minus start), skip HOUR entirely and convert the day fraction directly — multiply by 24:
Classic shift test — flag anything starting before 9 AM or after 5 PM:
Remember: HOUR answers “what hour does the clock show?” — not “how many hours is this?”. For elapsed hours, multiply the difference by 24.
Errors & common pitfalls
#VALUE! — text Excel can’t read as a time. =HOUR("2:30 PM") works, but =HOUR("half past two") or a time with stray characters fails. Clean the text first, or parse it explicitly with TIMEVALUE.
Pitfall: expecting 12-hour numbers. HOUR returns 0–23. For 11:30 PM it returns 23, never “11 PM”. Need a 12-hour label? Use =TEXT(A2, "h AM/PM") instead.
Pitfall: using HOUR on durations. A duration cell showing 26:00 (format [h]:mm) stores 1.0833 days — and =HOUR of it returns 2, because HOUR ignores whole days. For elapsed hours use =A2*24 or =INT(A2*24).
Pitfall: midnight returns 0. Both a date-only cell (no time part) and a genuine 12:00 AM stamp give =HOUR(...) = 0. If zeros look suspicious, check whether your timestamps actually contain times.
Practice workbook
Frequently asked questions
Why does HOUR return 14 instead of 2 PM?
=TEXT(A2,"h AM/PM").How do I get total elapsed hours between two times?
=(B2-A2)*24 returns 8.5 for a shift from 9:00 AM to 5:30 PM. Excel times are day fractions, so ×24 converts to hours.Does HOUR work on text like "6:45 PM"?
=HOUR("6:45 PM") returns 18, because Excel converts recognizable time text on the fly. Unrecognizable text gives #VALUE!. For imported data it’s safer to convert once with TIMEVALUE and reference real times.Why does HOUR return 0 for my dates?
How do I count rows that fall in a given hour?
=HOUR(A2) and count it: =COUNTIF(B:B, 14) for the 2 PM hour. Or in one step: =SUMPRODUCT(--(HOUR(A2:A500)=14)).Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class