Excel’s **HOUR **function is classified under two Excel functions which are **DATE** and **TIME**. It is one of the most popular functions in Excel in terms of time calculation. There are wide ranges of uses of this function for various purposes. In this article, I will try to cover the First, we will cover the theory portion, and then will discuss some uses of the **HOUR **function in Excel.

**HOUR Function in Excel (Quick View)**

In the following picture, you can see a quick view of** the HOUR function**.

**Table of Contents**hide

**Excel HOUR Function: Syntax & Argument**

**Summary**

The **HOUR** function returns the hour as a number from **0 (12:00 A.M) to 23 (11:00 P.M)**.

**Syntax**

`=HOUR(serial_number)`

As we can see it can take only one argument in its parameter.

**Arguments**

Argument |
Required or Optional |
Value |
---|---|---|

serial_number |
Required | The time that holds the hour you require to find. Times may be inserted as text strings within quotation marks (for example, “8:45 PM”), as decimal numbers (for example, 0.78125, which describes 6:45 PM) |

**Note:**

- 0.78125 means in a 24-hour clock system it is 18.75. And if you convert it into 12 Hours clock system, it is 6:45 PM)
- Sometimes we may need the results of other formulas or functions (for example,
**TIMEVALUE(“6:45 PM”)**).

In the following dataset, you can see **ID**, **Name**, and **Entry Time** columns. Next, using this data table**,** we will go through** 7** easy and compelling examples to see the **use of the HOUR function**. To do so, we will use** EXCEL 365**. You can use any available Excel version.

*[ Note: Here all the hours are shown based on a 24-hour clock system]*

**1. Using HOUR Function to Calculate Hour from Time in Excel**

In this example, we will find out the **Entry Hour **from the **Entry time **column using **the HOUR function**.

**Steps:**

- First, we will type the following formula in cell
**E5**.

`=HOUR(D5)`

- After that, press
**ENTER**.

As a result, you can see the result in cell **E5**.

- Further, we will
**drag down**the formula with the**Fill Handle tool**.

Therefore, you can see the complete **Entry Hour** column.

**2. Using HOUR Function by Ignoring Minute**

In this example, we will show that **the HOUR function** only considers the hour from any given time, and it won’t consider any minutes. Here, we modified the same dataset by adding minutes to the **Entry Time** column.

**Steps:**

- In the beginning, we will type the following formula in cell
**E5**.

`=HOUR(D5)`

- After that, press
**ENTER**.

Therefore, you can see the result in cell **E5**.

- Next, we will
**drag down**the formula with the**Fill Handle tool**.

Hence, you can see the complete **Entry Hour** column.

**3. Calculating Hour from Date in Excel**

We know that the **HOUR **function ignores the date and considers the only hours from any given time. In this example, we will show this feature. Here,** we add** a date with time in the** Entry Time **column. The formula will remain the same.

**Steps:**

- First of all, we will type the following formula in cell
**E5**.

`=HOUR(D5)`

- After that, press
**ENTER**.

As a result, you can see the result in cell **E5**.

Moreover, we will **drag down** the formula with the **Fill Handle tool**.

Therefore, you can see the complete **Entry Hour** column.

**4. Using TIME and HOUR Functions in Excel**

In this example, we will use the **HOUR** function with **the TIME function** to get a proper time. Before going to the main section, let’s see the basics of the **TIME **function.

`=TIME(hour, minute, second)`

As we can see it can take three arguments in its parameter.

Argument |
Details |
---|---|

hour |
The hour is for the time you wish to generate. |

minute |
The minute is for the time you wish to generate. |

second |
The second is for the time you wish to generate. |

Now we will insert an hour into the **TIME **function using the **HOUR **function. And again, we will consider the same dataset above. Our target is to add each **Entry Time** by 40 minutes which will be shown in a new column named **Updated Time**.

**Steps:**

- In the beginning, we will type the following formula in cell
**E5**.

`=TIME(HOUR(D4),40,0)`

**Formula Breakdown**

- The inner function which is
**HOUR(D4)**returns the hour from the**Entry Time**The return value will be 0 to 23 decimal numbers. - Then the
**TIME(HOUR(D4),40,0)**full function calculates the final update time. The second argument is 40 as we want to increase 40 minutes. And for a second, we have used 0 as we don’t need to put any value on that.

- Next, press
**ENTER**.

Therefore, you can see the result in cell **E5**.

- Next, we will
**drag down**the formula with the**Fill Handle tool**.

Therefore, you can see the complete **Updated Time** column.

**5. Calculating Differences Between Times in Excel**

In this example, we will use the** HOUR **function to find out the difference between times. In-office, sometimes we may need to calculate employees’ total working hours. For this purpose, we can get the help of this **HOUR **function. We will use the same dataset, but an extra column named **Leaving Time**. Here, we will find the difference between **Entry Time** and **Leaving Time**, and hence, we will get **Working Hours**.

**Steps:**

- First, we will type the following formula in cell
**F5**.

`=ABS(HOUR(D4)-HOUR(E4))`

**Formula Breakdown**

- For finding the absolute differences I have used the
**ABS**This will help to get values by ignoring the negative sign. **HOUR(D4)-HOUR(E4**These two**HOUR**functions extract hours from Entry time and Leaving Time and then do subtraction among them.- Finally,
**ABS(HOUR(D4)-HOUR(E4))**returns the absolute difference of the two hours.

- After that, press
**ENTER**.

Then, you can see the result in cell **F5**.

- At this point, we will drag down the formula with the
**Fill Handle tool**.

Hence, you can see the complete** Working Hours **column.

**6. Entering Time as Text in HOUR Function**

Here, we will use the **Time** as text in **the HOUR function**.

**Steps:**

- First of all, we will type the following formula in cell
**C5**.

`=HOUR("12:00")`

- After that press
**ENTER**.

As a result, you can see the result in cell **C5**.

- Further, in the same way, we type the following formula in cell
**C6**.

`=HOUR("12:00 AM")`

- Next, press
**ENTER**.

Therefore, you can see the result in cell **C6**.

In a similar way, we find out the result for **10:00 PM** and **9:00 AM**.

**7. Applying Excel HOUR Function for Decimals Time**

In this example, you can see in the **Value **column that the time is set as decimals. Next, we will use** the HOUR function** to find the time.

**Steps:**

- First, we will type the following formula in cell
**C5**.

`=HOUR(B5)`

- After that press
**ENTER**.

Then, you can see the result in cell **C5**.

- Afterward, we will
**drag down**the formula with the**fill Handle tool**.

Therefore, you can see the complete **Result** column.

**Common Errors While Using HOUR Function in Excel**

Here, we will discuss the errors that are shown by the **HOUR** function, and we will show the reasons why that types of errors occur.

Common Errors |
When They Show |
---|---|

#VALUE! |
The cells containing time and date should be invalid time and date format. Otherwise, this function will return #VALUE! |

#NUM! |
If the given input which is known as serial_number is out of range, then the HOUR function will return #NUM! |

**How to Use Time with Milliseconds in Excel**

Here, in the following dataset, you can see a **Time** column. Next, we will find out the **Time with Milliseconds **using the **Custom Number Format**.

**Steps:**

- First of all, we will select cells
**B5:B9**, and then we will copy these cells by using the keyboard shortcut**CTRL+C**.

- Afterward, to paste the copied cells, we will select cell
**C5**and press**CTRL+V**.

As a result, you can see the copied cells in cells **C5:C9**.

Then, we will format these cells.

- To do so, we will select cells
**C5:C9**>> go to the**Home**tab. - After that, from the
**Number**group >> select the**Number Format**icon marked with a*red color box*.

At this point, a **Format Cells** dialog box will pop up.

- Next, we will select
**Custom**from the**Category.** - Then, in the
**Type**box, we will type**h:mm:ss.000**as the format. - Moreover, click
**OK**.

As a result, you can see the complete **Time with Milliseconds** column.

**Practice Section**

In each Excel sheet of the above Excel file, there is a practice section where you can practice the above methods.

**Download Practice Workbook**

**Conclusion **

This is all about the **HOUR **function and its different applications. Overall, in terms of working with time, we need this function for various purposes. I have shown all the methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any other method of achieving this, then please feel free to share it with us. Also, if you have any questions, feel free to contact ask through the comment section. Thank you!