Purpose
Return value
Syntax
=HOUR(serial_number)
- serial_number - A valid Excel time.
How to use
The HOUR function returns the hour portion of a time as a number between 0-23. For example, with a time of 9:00 AM, HOUR will return 9. HOUR takes just one argument, serial_number, which must be a valid Excel date or a valid Excel time.
Times can be supplied to the HOUR function as text (e.g. "7:45 PM") or as decimal numbers (e.g. 0.5, which equals 12:00 PM). To create a time value from scratch with separate hour, minute, and second inputs, use the TIME function.
The HOUR function will "reset" to 0 every 24 hours (like a clock). To work with hour values larger than 24, use a formula to convert time to decimal hours.
Example #1 - Hour from time
The HOUR function returns the hour from of a time or date as a number between 0-23. For example:
=HOUR("9:00 AM") // returns 9
=HOUR("9:00 PM") // returns 21
Example #2 - Minutes ignored
The HOUR function ignores minutes and seconds. For example, when given the time "6:30 PM", HOUR returns 18:
=HOUR("6:30 PM") // returns 18
Example #3 - Hour from date
Some Excel dates include time. When given a date that includes time, the HOUR function will extract the hour and ignore the date. For example, with 29-May-2021 6:00 AM in cell A1:
=HOUR(A1) // returns 6
The date portion of the value is ignored completely. If the date contains no time value, HOUR returns 0 (zero) which is midnight.
Example #4 - with TIME function
You can use the HOUR function to extract the hour and feed the result into another formula, like the TIME function. For example, with the time "8:00 AM" in A1, you could force the time to be on the half-hour with:
=TIME(HOUR(A1),30,0) // returns 8:30 AM
Note: Excel stores dates and times as serial numbers. For example, the date Jan 1, 2000 12:00 PM is equal to the serial number 32526.5 in Excel. To check that Excel is correctly recognizing a date or time, you can temporarily format the date as a number.
Notes
- HOUR returns #VALUE! if serial_number is not recognized as a valid date or time.
- HOUR returns #NUM! if serial_number is out of range.