Excel HOUR, MINUTE and SECOND: Extract Parts of a Time
Pull the hour, minute or second number out of a time.
These three take a time apart, exactly as YEAR, MONTH and DAY take a date apart. HOUR returns 0 to 23, MINUTE returns 0 to 59, and SECOND returns 0 to 59.
Their common job is grouping. A column of timestamps cannot be summarised by hour directly, but =HOUR(B2) gives a helper column that a pivot table or SUMIFS can group on — which is how you find out when your busiest hour actually is.
There is one trap, and it comes straight from Excel storing times as fractions of a day. HOUR only ever returns the time-of-day part, so a *duration* of 30 hours returns 6, not 30. To get total hours from a duration you multiply by 24 instead: =(C2-B2)*24. Reaching for HOUR on a duration is the single most common mistake with these functions.
Syntax
=HOUR(serial_number) =MINUTE(serial_number) =SECOND(serial_number)Arguments
serial_numberRequired- The time to take apart. It must be a real time value — text that looks like a time returns #VALUE!.
The example data
Headers in row 1, data in A2:D5. Columns B and C hold real times.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Staff | Clock in | Clock out | Break (mins) |
| 2 | Alice Moreau | 08:15 | 16:45 | 30 |
| 3 | Bruno Santos | 13:00 | 21:30 | 45 |
| 4 | Chen Wei | 22:00 | 06:00 | 60 |
| 5 | Dana Okafor | 09:30 | 14:00 | 0 |
Worked examples
=HOUR(B2)Result: 8
The hour Alice clocked in, as a number you can group and compare on.
=MINUTE(B2)Result: 15
The minutes past the hour, ignoring which hour it is.
=HOUR(C2 - B2)Result: 8
This works only because the shift is under 24 hours. On a longer duration it would silently be wrong.
=(C2 - B2) * 24Result: 8.5
The correct way to get total hours from a duration. It does not cap at 24 and gives you the fraction too.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
HOUR returns 6 on a 30-hour total
Why it happens: HOUR only reads the time-of-day part. A duration over a day loses its whole days entirely.
How to fix it: Multiply by 24 instead: =(end - start) * 24 gives the real total including fractions.
#VALUE!
Why it happens: The argument is text that looks like a time rather than a real time value.
How to fix it: Convert with TIMEVALUE, or fix the column — real times are right-aligned, text is left-aligned.
Grouping by HOUR merges different days
Why it happens: HOUR discards the date, so 9am on Monday and 9am on Friday land in the same bucket.
How to fix it: Group on =TEXT(B2, "yyyy-mm-dd hh") if the day matters as well as the hour.
Tips worth knowing
- Total minutes from a duration is =(end - start) * 1440, since a day holds 1440 minutes.
- MINUTE on a duration is safe; only HOUR caps, because only hours roll into days.
- =TEXT(B2, "hh:mm") formats a time for display, where these three are for logic.
- Round a time to the nearest quarter hour with =MROUND(B2, TIME(0, 15, 0)).
Frequently asked questions
Why does HOUR give the wrong answer on a duration?
Because it returns the time-of-day component, which resets every 24 hours. A duration of 30 hours has a time-of-day part of 6:00, so HOUR returns 6. For total hours, multiply the duration by 24 instead.
How do I convert a duration to total minutes?
Multiply by 1440, the number of minutes in a day: =(C2-B2)*1440. The same logic gives seconds with 86400 and hours with 24.
How do I find the busiest hour in a log?
Add a helper column with =HOUR(timestamp), then COUNTIFS or a pivot table grouped on it. Include the date as well if the log spans more than one day, otherwise every Monday 9am is counted with every other day's.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.