Excel HOUR Function (Example + Video)
When to Use Excel HOUR Function
Excel HOUR function can be used when you want to get the HOUR integer value from a specified time value.
What it Returns
It returns a value between 0 (12:00 A.M.) and 23 (11:00 P.M.) depending on the time value used as the input.
Syntax
=HOUR(serial_number)
Input Arguments
- serial_number: the time value that contains the hour that needs to be determined.
Additional Notes
- The argument can be provided in different formats:
- As a serial number: The integer part of the serial number represents the date and the decimal portion represents time. For example, if you are using 41640.5 as the serial number, it would return 12 (as the decimal portion 0.5 represents 12:00 PM, which is half of a full day)
- As text: =HOUR(“12:00 PM”) would return 12.
- As a result of some other formula. For example, =TIMEVALUE(“6:45 PM”)
-
- Excel stores dates and time as serial numbers.
Excel HOUR Function – Examples
Here are five practical examples of using the Excel HOUR function.
Example #1 – Entering Time as Text
Excel Hour function can take the time within double quotes as the input. It then returns the integer between 0 and 23 based the time. For this to work, the time has to be in a format that Excel interprets as time.
If you enter the time without AM/PM (as shown in A2 in the above example) Excel will consider it as the time in the 24-hour format.
Make sure you enter time in a format that is recognized by Excel. For example, “12 Noon” wouldn’t count as a valid format, and would return a #VALUE! error.
Example #2 – Enter Cell Reference that has the Time
As shown in the examples above, you can use the cell reference that contains the time value. Note that if you don’t use AM/PM, the HOUR function would take it in the 24 Hour format.
Example #3 – Using Numerical Values in Exel Hour Function
If you use a numeric value within Excel HOUR Function, it will use the decimal portion of it to return the HOUR value. For example, if I use 41699.125 as the input in HOUR function, it will discard the 41699 part and use .125 part to return the time. The answer would be 3 as .125 represents three hours.
Example #4 – Using the Result of Excel HOUR function in Calculations
The output of the Excel HOUR function is an integer which can be used in formulas/calculations. For example, you can add/subtract values from the result of the HOUR function as shown above.
Excel HOUR Function – Video
Other Useful Excel Functions:
- Excel DAY Function: Excel DAY function can be used when you want to get the day value (ranging between 1 to 31) from a specified date. It returns a value between 0 and 31 depending on the date used as the input.
- Excel MINUTE Function: It can be used when you want to get the MINUTE integer value from a specified time value. It returns a value between 0 and 59 depending on the time value used as the input.
- Excel NOW Function: It can be used to get the current date and time value.
- Excel SECOND Function: It can be used want to get the integer value of the seconds from a specified time value. It returns a value between 0 and 59 depending on the time value used as the input.
You May Also Like the Following Tutorials:
