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.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.