Excel HOUR Function

HOUR extracts the hour portion of a time value and returns it as a whole number between 0 and 23. Feed it 2:45 PM and it returns 14. Feed it a full date and time, like 7/18/2026 2:45 PM, and it still returns 14: the date is ignored completely. The part that surprises people is the 24-hour reset. HOUR behaves like a clock face, not a stopwatch, so it can't tell you a shift ran 30 hours straight. It can only tell you what hour a given moment falls on.

Syntax

=HOUR(serial_number)

Parameter	Required	Description
`serial_number`	Yes	The time value to pull the hour from. Accepts a time, a date with a time attached, a decimal (0.5 = noon), or text Excel recognizes as a time, like "7:45 PM".

Basic Example

You're reviewing a helpdesk log where column C holds the exact timestamp each ticket was submitted. To find out which hour of the day a ticket came in:

=HOUR(C2)

// C2 = 7/18/2026 2:45:30 PM

HOUR strips out everything except the hour. The date, the minutes, the seconds, all of it gets discarded. This formula returns 14, meaning the ticket landed sometime between 2:00 PM and 2:59 PM. That single number is what makes HOUR useful for grouping timestamps into buckets: hourly call volume, peak order times, shift coverage gaps.

How HOUR Works

HOUR ignores minutes and seconds entirely

HOUR only cares about the hour component. A time of 6:59 PM and a time of 6:01 PM both return 18. If you need finer granularity, pair HOUR with MINUTE, or round the underlying time before extracting.

HOUR ignores the date portion of a value

If serial_number is a full date-time value, HOUR extracts the hour and throws away the date. =HOUR("3/1/2026 9:15 AM") returns 9, regardless of what the date is. If the value has no time component at all (just a bare date), HOUR returns 0, since midnight is the implied time.

Time entered as text works the same as a real time value

HOUR accepts text strings that look like times, such as "7:45 PM" or "23:10", and converts them automatically. This only works if Excel recognizes the string as a valid time format for your locale. A string like "quarter past 7" won't parse and throws an error.

HOUR resets every 24 hours, like a clock

HOUR never returns a number above 23. Add 24 hours to a time and HOUR reports 0, not 24. Add 30 hours and it reports 6, not 30.

⚠

HOUR cannot represent elapsed time past 24 hours. If you're tracking something like total machine runtime or a shift that spans midnight and beyond, use (end_time - start_time) * 24 instead of subtracting HOUR values. HOUR alone will silently give you a number that looks plausible but is wrong.

Common Use Cases

Bucketing timestamps into shift categories

You're building a call-center dashboard and want to label each call as Morning, Afternoon, Evening, or Night based on when it came in.

=IF(HOUR(B2)<12,"Morning",IF(HOUR(B2)<17,"Afternoon",IF(HOUR(B2)<21,"Evening","Night")))

// B2 = call timestamp
// HOUR(B2) < 12   returns Morning for anything before noon
// HOUR(B2) < 17   returns Afternoon for anything before 5 PM
// HOUR(B2) < 21   returns Evening for anything before 9 PM, otherwise Night

Converting worked time into decimal hours for payroll

A time card shows a shift as 7:45, meaning 7 hours 45 minutes. Payroll systems usually want that as a decimal.

=HOUR(D2)+MINUTE(D2)/60

// D2 = 7:45
// HOUR(D2) = 7
// MINUTE(D2)/60 = 0.75
// Result: 7.75

✓

MOD(D2,1)*24 gets you the same decimal hours in one step instead of two. Both approaches are floating-point math under the hood, so neither is inherently safer from rounding drift in every case, but HOUR(D2)+MINUTE(D2)/60 reads more clearly to someone auditing the formula later, since the hour and minute components stay visibly separate.

Counting entries per hour with SUMPRODUCT

You have a spilled range of order timestamps in orders and want to count how many landed during hour 14 (2 PM) without adding a helper column.

=SUMPRODUCT((HOUR(orders)=14)*1)

// orders = a range or dynamic array of timestamps
// HOUR(orders)=14 returns TRUE/FALSE for every value in the range
// *1 converts TRUE/FALSE into 1/0 so SUMPRODUCT can total them

This works directly on a spilled dynamic array reference, so it recalculates automatically as the source list grows.

Handling Errors

HOUR throws #VALUE! when serial_number is text Excel can't parse as a time. This is the main error case worth planning around. A negative time value doesn't error the way it might seem it should: HOUR wraps it the same way it wraps a value over 24 hours, reading the hour off the equivalent clock position rather than flagging the value as invalid.

Common causes of #VALUE!:

The cell contains plain text that isn't formatted as a time, like "afternoon" or "2:45p"
A reference to an empty cell, formula error, or the wrong column entirely
A locale-specific separator Excel doesn't recognize, like "7.45" typed where a colon is expected

=IFERROR(HOUR(C2), "Check time format")

✓

Don't reach for IFERROR just to hide a bad reference. If HOUR keeps throwing #VALUE! on a whole column, check the source data with ISNUMBER(C2) first. If it returns FALSE, the times are stored as text and need converting with TIMEVALUE, not just wrapped in an error catcher.

Notes & Gotchas

How do I use the HOUR function in Excel?

Type =HOUR(cell_reference) where the cell holds a time, a date-time value, or a text string Excel recognizes as a time. For example, =HOUR(A2) where A2 contains 3:30 PM returns 15. No second argument is needed; HOUR takes exactly one input.

What is the difference between the HOUR function and formatting a cell to show hours?

Formatting a cell to display "h" only changes how the underlying value looks on screen. It doesn't change the value itself, and you can't reference that displayed hour in another formula. HOUR actually extracts the hour as a real number you can use in calculations, comparisons, or lookups. If you need the hour as data, not just as a label, you need HOUR.

Why does HOUR return a #VALUE! error?

It means the input isn't something Excel can interpret as a time, usually a text string typed in an unrecognized format, like "quarter past 7" or a value pulled from the wrong column entirely. Check the source cell with ISNUMBER() to confirm it's a real time value before troubleshooting further.

How can I calculate the total hours between two times in Excel?

Subtract the start time from the end time, then multiply by 24: =(end_time - start_time) * 24. Don't subtract HOUR values directly, because HOUR resets at 24 and can't represent elapsed time past a single day. If the shift crosses midnight, add 1 to the result before multiplying, or the subtraction will return a negative number.

How do I extract only the hour, ignoring the date, from a date-and-time value in Excel?

Just wrap the cell in HOUR: =HOUR(A2). HOUR discards the date portion automatically and returns only the hour, so you don't need to strip the date out first. If A2 contains 11/22/2026 6:30 PM, =HOUR(A2) returns 18.

Why does HOUR reset to 0 after 24 hours?

Excel stores time as a fraction of a single day, where 1.0 equals 24 hours. HOUR extracts a value between 0 and 23 based on that fraction, so once you cross a full day, the fractional part loops back to 0. This is by design. It mirrors how a physical clock works, not how a stopwatch works.

What happens if the time value is negative?

HOUR doesn't error on it. A negative time value wraps the same way a value over 24 hours does: HOUR reads off the equivalent hour on the clock face rather than flagging the value as invalid. A shift subtraction that comes out to -1 hour, for instance, returns 23 from HOUR, not an error. That's easy to miss, since the cell itself will often display as a row of pound signs (########) if it's formatted as a time, which looks like a problem even though HOUR's own result is silently returning a plausible-looking but misleading number underneath. Check subtraction order carefully whenever a shift might cross midnight; that's the most common way negative times slip in, and the fact that HOUR doesn't complain is exactly why it's easy to miss.

Related Functions

Function	Use this when...
`TEXT`	You want to display a time in a custom format like "hh:mm AM/PM" without changing the underlying value.
`MINUTE`	You need the minute component alongside the hour, like building decimal hours for payroll.
`TIMEVALUE`	Your time is stored as text and needs converting into a real time value before HOUR can read it.