HOUR Excel

The HOUR Excel function is an inbuilt Date & Time function. It accepts a time value and determines the hour of the time value to return the output as an integer value in the range 0-23 (12:00 AM-11:00 PM).

Users can use the HOUR Excel function to extract the hour part of a specified time and in formulas requiring the hour values as integers.

For example, the following dataset lists time values in column A.

The aim is to extract the hours from the given time values and display the output in column B.

Then, we can use the HOUR Excel formula in each target cell to achieve the required output.

In the above HOUR Excel formula example, the HOUR() in each target cell accepts the cell reference to the time value in the corresponding row as input. It then returns the hour of the specified time value as an integer.

For instance, the time value in cell A2 is 10 hours, 20 minutes, and 15 seconds. So, the HOUR Excel function accepts the cell A2 time value as input and extracts the hours, 10, from it to return the value as the required output.

In the case of row 3, the HOUR() accepts a date-time value and returns the hours from the time component, 6, as the output while ignoring the date part of the input.

On the other hand, in row 4, the HOUR() accepts the decimal value 0.8 to return 80% of 24 hours as the hour part, 19.

Next, in row 5, the HOUR() accepts the time value which exceeds 24 hours. Thus, the function counts 24 hours as a day to ignore it and returns the remaining 1 hour as the required hour value as an integer.

Finally, in row 6, the HOUR() accepts a straightforward time value and returns the hour component of the input value, 8, as the output.

• The HOUR Excelfunction accepts a valid time value as input and returns the hour component of the specified time value as an integer.
• Users can use the Excel HOUR function in formulas requiring only the hour part of a time value while ignoring the date, minutes and seconds.
• The Excel HOUR function accepts a mandatory argument, serial_number, as input. We can supply the argument value as a text string in double quotes, a cell reference to an Excel time value, or a formula returning a valid time value.
• We can use the Excel HOUR function with other inbuilt functions, such as TIME, for practical results.

The HOUR Excel function syntax is as follows:

Where,

• serial_number: The time value containing the hour component we aim to find.

The HOUR Excel function argument is mandatory. It can be a text string within double quotes, a decimal number, a valid time value, a cell reference to a valid time value, a function or a formula returning a valid time value. Otherwise, the function will return the Excel #VALUE! error value.

Please note that the HOUR Excel function follows the 24-hour Clock system. So, if the time value is in AM, the function returns the hour component as in the input time value, except 12 AM, for which the HOUR() output will be 0. But if the time value is in PM, the function output will be 13, 14,and so on to 23, indicating 1 PM, 2 PM, and so on to 11 PM. The time value of 12 PM as input to the HOUR() will result in the function returning the hour component of the input time value as 12.

Furthermore, the time values are a segment of a date value and are denoted by a decimal value. For instance, the value 0.5 interprets as 12:00 PM, i.e., mid-day, and every 0.05 added to the decimal value indicates an increase in time by one hour.

We can use the HOUR Excel function in two ways:

1. Access the function from the Excel ribbon.
2. Enter the function into the worksheet manually.

Method #1 – Access The Function From The Excel Ribbon

Choose the target cell for output - The Formulas tab - The Date & Time function group down arrow - HOUR function to get HOUR Excel function into the target cell.

The Function Arguments window will open. Enter the argument in the Serial_number field.

Finally, clicking OK in the Function Arguments window will close the window, and we will get HOUR Excel function output in the target cell.

Method #2 – Enter The Function Into The Worksheet Manually

1. Choose a target cell to display the output.
2. Type =HOUR( in the cell.
3. Enter the argument as a value, reference, or formula, and close the brackets.
4. Press Enter to execute the HOUR() and view the required hour component of the supplied time value.

The following examples show the applications of the HOUR Excel function.

Example #1 - Return Hour From Time

The following dataset contains the experiment numbers and their test times.

The aim is to determine the hour of the test during each experiment based on its test time and display the output in column C.

Then, we can use the HOUR Excel function in each target cell to obtain the required output.

Step 1: Choose cell C2, enter the HOUR(), and press Enter.

=HOUR(B2)

Otherwise, we can supply the input cell B2 time value as a text string in double quotes to the HOUR Excel function in cell C2.

=HOUR("13:45")

Step 2: Using the Excel fill handle, update the formula in the remaining target cells C3:C6.

In the case of Exp_1 and Exp_3, the HOUR() in each target cell returns the hour component of the corresponding test time value, 13 and 16.

On the other hand, the hour part in Exp_2 and Exp_4 test time values is 2. However, the HOUR() output differs for the two experiments. The reason is that Exp_2’s test time value is in PM and that of Exp_4 is in AM, and the HOUR Excel function follows the 24-hour Clock system. Thus, the function returns the hour value accordingly, with the hour value of 2 in cell C3 shown as 16, while it remains as 2 in cell C5.

Finally, the Exp_5’s test time value exceeds 24 hours. Thus, the HOUR() ignores the first 24 hours of the time value since it equals one day. Next, the remaining time value is 9:20:30 AM, leading to the HOUR() returning the required hour value as 9.

Example #2 - Return Hour From Date

The following dataset contains a list of branch offices and their inventory delivery schedules in the date-time format.

The requirement is to extract the hour segment from each inventory delivery schedule date-time value and display the output in column C.

Then, here is how to use the HOUR Excel function in the target cells and obtain the required outcome.

Step 1: Select cell C2, enter the HOUR(), and press Enter.

=HOUR(B2)

Step 2: Using the fill handle, update the HOUR Excel function in the remaining target cells.

The HOUR Excel function in each target cell accepts the date-time value. It ignores the date part in the input value. Next, it returns the hour component of the time value, with the output depending on whether the input time value is in AM or PM.

However, cell B5 contains only a date and does not show the time section. In such cases, the time is considered to be 12:00:00 AM, and the HOUR Excel function returns the value of 0.

Example #3 - Return Hour Ignoring Minutes

We shall see how to return hour values while ignoring minutes specified in the input time values.

The following dataset lists agency resources and their swipe-in/out times on two days.

The task is to determine the total hours clocked in the two days by each agency resource, ignoring the minutes, and display the output in column D.

Then, the steps are as follows:

Step 1: Choose the range D3:D7 and set Home - Number Format - Number.

Step 2: Choose cell D3, enter the following formula, and press Enter.

=HOUR(B3-$C$1)+HOUR(C3-$C$1)

Step 3: Using the fill handle, update the formula to add HOUR Excel functions in the remaining target cells.

Let us check the cell D7 formula to understand the logic.

The formula finds the difference between the agency resource’s swipe out and in timings for days 1 and 2 inside the first and second HOUR(). The differences will be time values in decimals, 0.27083 and 0.125.

Next, the HOUR Excel functions extract the hour components from the resulting time values and return the values of 6 and 3 for days 1 and 2. Thus, we get the required hours, with the minutes ignored.

Finally, the formula helps add HOUR Excel functions’ output values to return 9 as the required total hours clocked by the corresponding agency resource.

Example #4 - Return Hour Using TIME Function

We shall see how to round time to nearest hour Excel using the TIME and HOUR functions.

The following dataset lists employees and their entry times.

The task is to round the employee’s entry time to the nearest lower hour if the entry time is within the deadline. Otherwise, the output should be the message “Entry Time Deadline Missed.”

Then, we can use the TIME and HOUR functions to create a round time to nearest hour Excel expression and use it in the target cells to achieve the required output.

Step 1: Choose the range D2:D6 and select Home - Number Format drop-down button - More Number Formats.

The Number tab in the Format Cells window will open, where we must choose the Time category and the required time format in the Type section.

Click OK in the Format Cells window to exit it.

Step 2: Choose cell D2, enter the following formula, and press Enter.

=IF(B2<$C$2,TIME(HOUR(B2),0,0),"Entry Time Deadline Missed.")

Step 3: Using the fill handle, enter the formula in the rest of the target cells.

Let us check the cell D6 formula to understand how it works.

First, the formula checks if the cell B6 entry time value is smaller than the cell C2 entry time deadline value as the Excel IF function condition. In this case, the IF condition holds.

So, the TRUE value gets executed, where first, the HOUR Excel function accepts the cell B6 entry time value as input to return the hour part of the time value, 7. Next, the Excel TIME function accepts the HOUR() output and two 0s as the input to return the resulting time value as a decimal, 0.29166.

Finally, since we set the target cell data format as Time, the IF() returns the obtained TRUE value in the specified time format, 7:00 AM.

• The HOUR Excel function argument value must be a valid time value recognized by Excel. Otherwise, the HOUR() will return the #VALUE! error.
• The Excel HOUR function follows the 24-hour Clock system, leading to its output ranging from 0 to 23.
• The Excel HOUR function ignores the date in a date-time value when supplied as an input.
• The time values supplied as input to the HOUR() are a segment of a date value and are shown as a decimal value.

This article must be helpful to understand the HOUR Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to What Is HOUR Excel. Here we learn the HOUR function syntax and how to use it in Excel with examples and points to remember. You can learn more from the following articles –

• TIMEVALUE Excel
• TIME Excel Function
• Formatting Time in Excel