Exceljet

Quick, clean, and to the point

Excel HOUR Function

Excel HOUR function
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)
Arguments 
  • serial_number - A valid Excel time.
Usage notes 

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

You can use the HOUR function to extract the hour into a cell, or 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

Times can be supplied as text (e.g. "7:45 PM") or as decimal numbers (e.g. 0.5, which equals 12:00 PM). In general, it's best to use the TIME function to create time values, because it reduces errors.

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.