Excel HOUR Function – How To Use

The HOUR function is categorized as a DATE/TIME function in Excel and extracts the hour component of a time. It returns a value between 0–23. You can use the HOUR function on its own or use it along with another function by passing the extracted HOUR part as a relayed result.

Syntax

The syntax of the HOUR function is as follows:

Arguments:

'serial_number' – This is a required argument that may be supplied with a valid, Excel-recognized time value from which we want to extract the hour component.

Important Characteristics of the HOUR function

• The serial_number argument may be supplied as a decimal value, text string representing a valid time, cell reference to a cell containing a valid time, or another function that relays a valid time.
• The HOUR function always returns a value between 0 and 23. For instance, if the time supplied to the function is 5:55 PM, the function will return 17.
• The HOUR function may be nested inside another function such as the TIME function.
• If the value supplied in the serial_number argument isn't a valid Excel date, the function returns a #VALUE error.
• If the value supplied in the serial_number argument is out of the valid range, the function returns a #NUM error.

Recommended Reading: Excel Minute Function

Examples

Let's try to see some examples of the HOUR function.

Example 1 – Plain Vanilla Formula for the HOUR Function

Here's a simple use case of the HOUR function. To begin with, we'll just extract the hour component from time in a given cell using the following formula:

The HOUR function follows the 24-hour time. Accordingly, the function extracts the hour component from "5:55 PM" and returns 17. Note that the HOUR function completely ignores the minute component. For instance, if you extract the hour component from 5:30 PM, the output will still be 17 and not 17.5.

Example 2 – Create Time Using the HOUR Function

The HOUR function may also be nested inside another function to relay the hour component. The TIME function requires all three components (i.e., hours, minutes, and seconds) as arguments to create a time. Therefore, we may use the HOUR function to supply the hour component to the TIME function like so:

The output we get, of course, picks out the hour component from cell A2 and uses the values supplied as arguments for the minute and seconds components. The final output, therefore, is 17:45:30. If your output looks slightly different, ensure that you custom format the output cell appropriately.

Example 3 – Other Acceptable Inputs and Their Outputs

Often, your dataset may use different formats for time instead of the plain HH:MM format. For example, the time could have a date component attached to it, or the time could be supplied as a decimal value. The HOUR function accepts these formats of time, and several other formats too.

Following are some inputs (other than the usual HH:MM input) that the HOUR function accepts:

The HOUR function can extract the HOUR component out of all of these inputs. If the output for the last two inputs confuses you, we'll walk through the logic to see how the function arrives at that value.

Time with Hour in Excess of 24

An important characteristic of the HOUR function is that it always returns a value between 0 and 23. If you look at the output for the row where the hours are in excess of 24 (i.e., 30:30:30), the output is 6. The reason?

The HOUR function resets to 0 after 23. Whenever the number of hours is greater than 24, the HOUR function will begin the count from 1 again. In this case, this gives us an output of 6, which is calculated as 30-24.

Similarly, if the hours are set to 24, 48, 72, or any other multiple of 24, the function will return 0. If the hours are greater than 24, but not a multiple of 24, the return is calculated as X minus the immediately preceding multiple of 24.

Time Supplied as Decimal

When time is supplied as a decimal value, Excel calculates the time by multiplying it by 24 hours. For instance, in our case, we've supplied the value 0.7. This is the same as telling Excel that you want the time that occurs after 70% of a day has passed. Therefore, Excel computes the time as 0.7 x 24 hours, which comes to 16.8 hours.

The value after the decimal is truncated since the HOUR function ignores the minute component. Therefore, the final output is 16.

That takes care of a simple, yet useful function — the HOUR function. The HOUR function is often helpful for constructing larger formulas rather than as standalone. However, it's a handy function that can go a long way in making your life easy while you juggle with figures on a spreadsheet. While you get comfortable with the HOUR function, we'll put together another tutorial for you.