## What Is HOUR Function In Excel?

The

HOURExcel function is an inbuiltDate & Timefunction. 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

HOURExcel 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.

##### Table of contents

### Key Takeaways

- 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.

### Syntax

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.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tuto**rials)

**–>>** **If you want to learn Excel and VBA professionally**, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from **Basic Excel to Advanced Excel, Macros, Power Query, and VBA.**

### How To Use HOUR Function In Excel?

We can use the **HOUR **Excel function in two ways:

**Access the function from the Excel ribbon.****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

- Choose a target cell to display the output.
- Type
**=HOUR(**in the cell. [ Alternatively, type**=H**or**=HO**and double-click the**HOUR**Excel function from the listed suggestions to choose it.] - Enter the argument as a value, reference, or formula, and close the brackets.
- Press
**Enter**to execute the**HOUR()**and view the required hour component of the supplied time value.

### Examples

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”)*

[Alternatively, choose cell C2 and **Formulas **– **Date & Time **– **HOUR**.

The **Function Arguments** window will appear, where we can enter the cell reference to the time value in the corresponding row in the **Serial_number** field.

Clicking **OK** in the **Function Arguments** window will close the window, and we can view the required hour portion of the specified time value in the target cell.]

**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**.

### Important Things To Note

- 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.

### Frequently Asked Questions (FAQs)

**1. What is the Hour function in VBA?**

The **Hour **function in VBA is a function that returns the hour component of the specified time value. Its functionality is the same as that of the **HOUR()** in the **Date & Time** function group we use in Excel.

For example, the following dataset lists products and their order dispatch times.

The aim is to update the order dispatch hour value based on the order dispatch time value for each product and display the output in column C.

Then, here is how we can use the **HOUR **function in VBA to achieve the required output in the target cells.**Step 1: **Open the worksheet containing the source dataset and press **Alt **+ **F11 **to access the VBA Editor.

Step 2: Choose the applicable VBAProject and then the Module option under the Insert tab to access a new module.

Step 3: Type in the VBA code in the module to apply the HOUR() in the target cells.

image.

Step 4: Select the play icon in the menu to execute the VBA code.

Finally, we can open the source dataset worksheet to view the HOUR() output in the required target cells.

**2. How to add hour and minutes in Excel?**

We can add hour and minutes in Excel using the following formula:**=TIME(HOUR(serial_number),MINUTE(serial_number),SECOND(serial_number))+TIME(0, MINUTE(serial_number),0)**

The **serial_number** argument is a valid time value.

**3. How do I calculate hours difference in Excel?**

You can calculate hours difference in Excel using the following **HOUR()**-based formula.**=HOUR(serial number_timevalue1- serial number_timevalue2)**

The **serial number_timevalue1 **must be bigger or at a later time than **serial number_timevalue2** to avoid potential errors.

### Download Template

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.

### Recommended Articles

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 –

## Leave a Reply