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.
Version 
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.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas