Summary

The Excel HOUR function returns the hour component of a time as a number between 0-23. For example, with a time of 9:30 AM, HOUR will return 9. You can use the HOUR function to extract the hour into a cell, or feed the result into another formula, like the TIME function.

Purpose 

Get the hour as a number (0-23) from a Time

Return value 

A number between 0 and 23.

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 argumentserial_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.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.