What is the Excel HOUR Function?
The Excel HOUR function returns the hour of time value as a number between 0 (12:00 AM) until 23 (11:00 PM)
Use the SECOND function to extract the second number and use the MINUTE function to extract the minute number
HOUR Syntax
HOUR(serial_number)
serial_number, required, a valid Excel time that contains the hour you want to find.
- The decimal numbers 0.78125 which represents 6:45 PM, the results of other formulas or functions (e.g., TIME(6,45,0)) or the text within quotation marks (e.g., “6:45 PM”) is accepted as a serial_number argument for the HOUR function.
- DATE and TIME can be written simultaneously; Excel stores them in integer and decimal numbers. The integer number represents the DATE value, and the decimal number represents the TIME value.
- For more explanation of how excel handles DATE and TIME, please read “How does Excel Store Dates and Times“
How to Use HOUR Function in Excel
For example, there are data such as the image below. There is seven TIME value in column A, what is the result of the HOUR function that uses the seven TIME value in column A as the serial_number argument.
HOUR Function #1
=HOUR(A2)
Cell A2 contains text with HH:MI:SS AM/PM time format, pay attention to the formula bar at the image above, there is a quotation mark at the beginning of the TIME value. Although a text, as long as Excel successfully converts to the TIME value, excel HOUR function will extract the hour number.
The result is 3.
HOUR Function #2
=HOUR(A3)
Cell A3 contains text as well as data in cell A2, but the format is HH24:MI:SS. Regardless of the format as long as Excel successfully converts it to the TIME value, the HOUR function will extract the hour number.
The result is 16.
HOUR Function #3
=HOUR(A4)
As explained in the article “How does Excel Store Dates and Times”, Excel stores TIME value in the form of decimal numbers.
The result is 5.
HOUR Function #4
=HOUR(A5)
The HOUR function #4 returns the same number as the HOUR function #5 because the TIME 5:05:55 in the form of a decimal number is 0.21244212962963.
HOUR Function #5
=HOUR(A6)
The serial_number argument is not for TIME value only; you can use a TIME value combined with DATE value as a serial_number argument for HOUR function. Excel remains to extract the hour number.
The result is 11.
HOUR Function #6
=HOUR(A7)
Cell A7 contains text, the same format as a text in cell A3, but there is an odd value. There should be no 70th seconds, but why does the HOUR function return no error?
If there is a second value exceeds 60, then Excel will add the minute value and display the remaining value in seconds. This valid for a minute and hour value too as long as the hour value does not reach 24.
The result is 23.
HOUR Function #7
=HOUR(A8)
Just like data in cell A7, there are 70th seconds, but why the HOUR function returns a #VALUE! error? because the hour value has reached 24.
Please see the image below for the complete results of the HOUR function in column B.
