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.
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!
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
