How to use HOUR Function in Excel (5 Easy Examples)

Overview of Hour function

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 I will cover theory portion and then I will discuss some uses of the HOUR function in Excel.

Download the Practice Workbook

HOUR Function in Excel (Quick View)

Overview of Hour function

Excel HOUR Function: Syntax & Argument

The syntax of this function is:

Excel HOUR Function Syntax & Argument

Summary

This 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:

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

How to Use HOUR Function in Excel (5 Examples) 

Example 1. Hour from Time Using HOUR Function in Excel

This function can easily extract hours from a given time. Let’s consider we have a dataset of some office employees with their ID, Name, Entry Time. Now we will find out the hour from the Entry time column and show them in a different column.

Hour from Time Using HOUR Function in Excel

Step 1: Enter the formula in cell E4 and copy it down up to E13

=HOUR(D4)

Enter the formual using Hour function

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

Example 2. Hour from Time Using HOUR Function by Ignoring Minutes

As we have mentioned earlier the HOUR function only considers the hour from any given time it won’t consider any minutes. Let try this by modifying the same example above. Here we will just change the Entry time and will use the sample formula for every cell.

Hour function ignoring minutes

Step 1: Enter the formula in cell E4 and copy it down up to E13

=HOUR(D4)

Formula using Hour function

Example 3. Hour from Date Using HOUR Function

We know that the HOUR function ignores the date and considers the only hours from any given time. Let’s try this by modifying Entry Time. We will add a date with time in the same column. The formula will remain the same.

Hour from Date Using HOUR Function

Step 1: Enter the formula in cell E4 and copy it down up to E13

=HOUR(D4)

Output of HOUR function

Try By Yourself!

Now you can check if the HOUR function considers seconds or not by using the same dataset. Just modify it and compare the outputs.

Example 4. Uses with TIME function

Now we will use this function with the TIME function to get a proper time. Before going to the main section let’s see the basics of 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 showed in a new column named Update Time.

Uses with TIME function

Step 1: Enter the formula in cell E4 and copy it down up to E13

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

Formula Breakdown

  • The inner function which is HOUR(D4) returns the hour from the Entry Time column. 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 for that.

Enter formula using Time and Hour function

Read more: How to Use SECOND Function in Excel and How to Use MINUTE Function in Excel

Example 5. Using HOUR Function for Calculating Time in Excel

Let’s see another use of the HOUR function for calculating time. In-office sometimes we may need to calculate employees total working hour. 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.

Using HOUR Function for Calculating Time in Excel

Step 1: Enter the formula cell F4 and copy it down up to F13

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

Formula Breakdown

  • For finding the absolute differences I have used the ABS function. 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.

formula using ABS and Hour function

Common Errors While Using HOUR Function

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!

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.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo