HOUR function

HOUR extracts the hour component from a time value, returning a number between 0 and 23 representing the hour of the day.

=HOUR(serial_number)

Generate a HOUR formula

Describe what you need. The generator will reach for HOUR where HOUR is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How HOUR reads its arguments
serial_numberrequiredHOUR →
ArgumentRequiredDescription
serial_numberRequiredA time value as a decimal, formula result (e.g., NOW() or TIME()), or text in time format. Times outside the range 0–1 (midnight to midnight) may cause #NUM! errors.

Returns

An integer from 0 to 23, representing the hour portion of the time.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Extract submission hour from a time value

StudentSubjectAssignmentScoreMaxSubmit Time
JaneMathQuiz 18510014:30
MikeMathQuiz 17810009:45
EmmaScienceLab9210022:15
=HOUR(TIME(14,30,0))

Result: 14

The TIME function creates a time value for 2:30 PM (14:30 in 24-hour format). HOUR extracts just the hour component, returning 14. This is useful to identify when students typically submit assignments and to detect patterns in submission timing.

2. Check if assignment was submitted during school hours

StudentSubjectAssignmentScoreMaxSubmit Time
JaneMathQuiz 18510014:30
MikeMathQuiz 17810009:45
EmmaScienceLab9210022:15
=IF(AND(HOUR(TIMEVALUE(B2))>=8, HOUR(TIMEVALUE(B2))<17), "During hours", "Outside hours")

Result: Outside hours (for Emma's 22:15 submission)

TIMEVALUE converts the text "22:15" to a time value. HOUR extracts the hour (22). The IF compares it to school hours (8 AM to 5 PM); since 22 falls outside that range, it returns "Outside hours". Use this to flag late or unusually timed submissions.

3. Categorize submissions by time of day

StudentSubjectAssignmentScoreMaxSubmit Time
JaneMathQuiz 18510010:00
MikeMathQuiz 17810014:30
EmmaScienceLab9210022:15
=CHOOSE(INT(HOUR(TIME(10,0,0))/6)+1,"Night (0–5)","Morning (6–11)","Afternoon (12–17)","Evening (18–23)")

Result: Morning (6–11)

HOUR returns 10 for 10:00 AM. INT(10/6)+1 = 2, so CHOOSE selects the 2nd option, "Morning (6–11)". This formula categorizes all submissions into broad time buckets, enabling analysis of whether certain times correlate with higher or lower assignment scores.

Common errors

Which HOUR error are you seeing?
HOUR returned an error#VALUE!
Wrap the text in TIMEVALUE first: =HOUR(TIMEVALUE("2:30 PM")) instead of =HOUR("2:30 PM").
#NUM!
Ensure your time input is between 0 and 1, or use TIME() to construct valid times: =HOUR(TIME(14,30,0)) instead of =HOUR(-5) or =HOUR(2.5).
#VALUE!
Verify the cell contains recognizable time data (HH:MM, HH:MM:SS, or text like "2:30 PM"). If reading from a cell, inspect its contents first; for dates, use =HOUR(NOW()) or =HOUR(DATEVALUE(A1)) as needed.
ErrorWhy it happensHow to fix it
#VALUE!Passing text like "2:30 PM" directly to HOUR without converting it to a time value first. HOUR expects a time serial number (decimal or time formula result), not a text string.Wrap the text in TIMEVALUE first: =HOUR(TIMEVALUE("2:30 PM")) instead of =HOUR("2:30 PM").
#NUM!Passing a number outside the valid time range (0–1). Times are stored as decimals where 1 = 24 hours; negative numbers or values greater than 1 are invalid time inputs.Ensure your time input is between 0 and 1, or use TIME() to construct valid times: =HOUR(TIME(14,30,0)) instead of =HOUR(-5) or =HOUR(2.5).
#VALUE!Passing a non-time text string like "hello" or "student" to HOUR after wrapping it in TIMEVALUE. TIMEVALUE cannot parse arbitrary text as a valid time format.Verify the cell contains recognizable time data (HH:MM, HH:MM:SS, or text like "2:30 PM"). If reading from a cell, inspect its contents first; for dates, use =HOUR(NOW()) or =HOUR(DATEVALUE(A1)) as needed.

Tips and when to use something else

  • HOUR returns 0 for midnight, 12 for noon, and 23 for 11 PM. It always uses 24-hour format (0–23), even if your spreadsheet displays times in 12-hour (AM/PM) format.
  • To extract minutes or seconds instead, use MINUTE() or SECOND(). For day, month, or year components, use DAY(), MONTH(), or YEAR()—not HOUR.
  • HOUR works on the time component only. If your serial_number is a full date-time value (e.g., from NOW()), HOUR ignores the date and extracts only the hour of that moment.
  • For elapsed time between two timestamps, calculate (end_time - start_time) * 24 rather than applying HOUR to the difference, or use DATEDIF() for more control.

Related functions

MINUTEMINUTE extracts the minute component (0-59) from a time value, returning an integer representing the minutes elapsed in the current hour.TIMEVALUEConverts time text into a decimal number enabling calculations with warehouse receipt times, shipping schedules, and processing timestamps.TIMETIME converts hours, minutes, and seconds into a decimal time value between 0 and 1, representing a time on a 24-hour clock.NOWReturns the current date and time as a timestamp value that updates automatically each time the spreadsheet recalculates.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

Why does HOUR return 0 when I expect 24 for midnight?
HOUR uses 24-hour format where midnight is hour 0, not 24. This is the standard in most spreadsheet systems. If you need 1–24 format instead, wrap it: =IF(HOUR(A1)=0, 24, HOUR(A1)).
How do I extract hours from a text time like "2:30 PM"?
Use TIMEVALUE to convert the text to a time value first: =HOUR(TIMEVALUE("2:30 PM")). If the time is in a cell, reference it: =HOUR(TIMEVALUE(A1)).
Can HOUR extract total hours from a duration or elapsed time?
Not directly. If A1 contains a time value (e.g., from =NOW()-DATEVALUE("2026-09-15")), HOUR extracts the hour-of-day component, not total elapsed hours. For elapsed hours, use =(end_time - start_time) * 24 or DATEDIF() for date spans.
Does HOUR handle time zones?
No. HOUR extracts only the time-of-day from the cell value; it has no awareness of time zones. If you need to adjust for time zones, add or subtract hours before calling HOUR, or use a dedicated time-zone function if your platform provides one.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17