Excel Functions
How to Use Excel HOUR Function?
HOUR function is used to return the hour as a number between 0 and 23 from a given time.
HOUR Function: A Brief
The HOUR function in Excel extracts the hour from a given time and returns a number between 0 and 23. For example, if the time is 10:30 AM, the HOUR function will return 10. You can use this function to return the hour value in a cell.
| Objective | Value Returned by function |
|---|---|
| Aim to get the Hour value as Number | HOUR Function will return a numeric values from the input hour value. |
HOUR Function: A Syntax:
=HOUR(hour_value)- hour_value: It should be a valid excel hour value.
Important Notes About HOUR Function:
- The HOUR function in Excel extracts the hour value from a given time and returns a number between 0 and 23.
- For Example, if the time is 11:00 AM, the HOUR function will return the numeric value as 11. It requires only one argument, serial_number, which must be a valid Excel date or Excel time.
- You can enter time values as text (e.g., “9:25 PM”) or as decimal numbers (e.g., 0.5, which equals 12:00 PM). The HOUR Function returns the equivalent numeric value.
- In the above image, The input value is 0.34 decimal value but the HOUR Function converts into a valid Excel time and returns the numeric value equivalent to the input hour value. So the HOUR function considers the decimal value as 08:09 AM and returns the numeric value as 8 in the output cell.
| Decimal Values | Equivalent Hour Value in Excel |
|---|---|
| 0.00 | 12:00 AM (Midnight) |
| 0.01 | 12:14 AM |
| 0.02 | 12:29 AM |
| 0.03 | 12:43 AM |
| 0.04 | 12:57 AM |
| 0.05 | 1:12 AM |
| 0.06 | 1:26 AM |
| 0.07 | 1:41 AM |
| 0.08 | 1:55 AM |
| 0.09 | 2:10 AM |
| 0.10 | 2:24 AM |
| 0.125 | 3:00 AM |
| 0.15 | 3:36 AM |
| 0.1667 | 4:00 AM |
| 0.20 | 4:48 AM |
| 0.25 | 6:00 AM |
| 0.30 | 7:12 AM |
| 0.3333 | 8:00 AM |
| 0.375 | 9:00 AM |
| 0.40 | 9:36 AM |
| 0.50 | 12:00 PM (Noon) |
| 0.55 | 1:12 PM |
| 0.5833 | 2:00 PM |
| 0.60 | 2:24 PM |
| 0.625 | 3:00 PM |
| 0.6667 | 4:00 PM |
| 0.70 | 4:48 PM |
| 0.75 | 6:00 PM |
| 0.80 | 7:12 PM |
| 0.8333 | 8:00 PM |
| 0.875 | 9:00 PM |
| 0.90 | 9:36 PM |
| 0.9167 | 10:00 PM |
| 0.95 | 11:24 PM |
| 1.00 | 12:00 AM (Midnight) |
From the above table, you can find the decimal values and equivalent hour values. The Decimal value should be lies between 0 to 1.
Example 1: Basic Example:
The below example will make you understand the HOUR Function easily.
| Input Time | Formula Used | Final Result | Comments |
|---|---|---|---|
| 10:45 AM | =HOUR(B3) | 10 | Returns 10 because the hour part is 10 |
| 5:30 PM | =HOUR(B4) | 17 | Returns 17 because 5 PM is represented as 17 in a 24-hour format |
| 00:15 | =HOUR(B5) | 0 | Returns 0, indicating midnight |
| 23:59 | =HOUR(B6) | 23 | Returns 23, indicating 11:59 PM |
| 12:00 PM | =HOUR(B7) | 12 | Returns 12, as noon is the 12th hour of the day |
Example 2: Return Hour Values from Date & Time Values:
If you sheet contains both date and time values in the cells, you can use the HOUR function to extract the numeric values by ignoring the date values from the input cell.
| Input Date and Time | Formula Used | Final Result | Comments |
|---|---|---|---|
| 03-08-2025 10:30 | =HOUR(B3) | 10 | Returns 10 because the time is 10:30 AM. |
| 25-12-2024 00:01 | =HOUR(B4) | 0 | Returns 0, as midnight is hour 0. |
Notes:
- If the hour_value is not recognised by HOUR function then it will returns the #VALUE! error.
- If the hour_value is out of given range and then the HOUR function will returns the #NUM! error.
That’s it.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.
References:
- HOUR function in Excel by Stackoverflow, Retrieved on 19/3/25: Link.
- How to extract hours from a date time value in Excel | How To Use Hour Function In Excel by The Data Millennials, Retrieved on 19/3/25: Link.
- HOUR function in Excel by Spotlight Impact Data Design, Retrieved on 19/3/25: Link.
- HOUR Function by Microsoft Support, Retrieved on 19/3/25: Link.
- HOUR Function by CFI Team, Retrieved on 19/3/25: Link.
