Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use HOUR Function in Excel (7 Easy Examples)

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.


Download Practice Workbook


HOUR Function in Excel (Quick View)

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

How to Use Hour Function in Excel


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

7 Examples of Using HOUR Function in Excel

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. Calculating Hour from Time

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

Using Hour Function in Excel

Steps:

  • First, we will type the following formula in cell E5.
=HOUR(D5)
  • After that, press ENTER.

Applying HOUR Function to Calculate Entry Hour

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

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

Calculating Entry Hour Using HOUR Function

Therefore, you can see the complete Entry Hour column.

Read More: Excel Current Time Formula (7 Suitable Examples)


2. Use of 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.

Applying HOUR Function Ignoring Minute

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.

Read More: How to Use MINUTE Function in Excel (6 Suitable Examples)


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.

Inserting HOUR Function by Ingonring Date

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.

Read More: How to Use DATE Function in Excel (10 Ideal Examples)


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)

Use of TIME and HOUR Function in Excel

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.

Use of HOUR Function and TIME Function to calculate Updated Time

Read More: How to Auto-Update Current Time in Excel (With Formula and VBA)


Similar Readings


5. Calculating Differences Between Times 

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

Using HOUR Function to Calculate Working Hours

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.

Use of Text in Excel HOUR Function

  • Further, in the same way, we type the following formula in cell C6.
=HOUR("12:00 AM")
  • Next, press ENTER.

Entering Time as Text in Excel Hour Function

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.

Using HOUR Funcion for decimals time in Excel

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.

Read More: How to Use SECOND Function in Excel (3 Suitable Examples)


Practice Section

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


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. You can also visit our official site ExcelDemy for various articles. Thank you


Related Articles

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