Part 1: Introduction to the HOUR Function in Microsoft Excel
Definition
The HOUR function in Microsoft Excel is a built-in function used to obtain the hour from a given time value. The hour is returned as an integer, ranging from 0 (12:00 AM) to 23 (11:00 PM).
Purpose
The purpose of the HOUR function is to extract the hour from a time value. This can be useful in various scenarios, such as calculating time durations, scheduling tasks, or analyzing data with time stamps.
Syntax & Arguments
The syntax for the HOUR function is as follows:
HOUR(serial_number) The HOUR function has the following argument:
serial_number: This is a required argument. It is the time that contains the hour you want to find. Times may be entered as text strings within quotation marks (for example, “6:45 PM”), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for instance, TIMEVALUE(“6:45 PM”)).
Return Value
The HOUR function returns the hour of a time value as an integer, ranging from 0 (12:00 AM) to 23 (11:00 PM).
Remarks
Time values are a portion of a date value represented by a decimal number. For example, 12:00 PM is 0.5 because it is half a day.
Part 2: Examples of Using the HOUR Function in Microsoft Excel
Example 1: Extracting the Hour from a Time Stamp
Purpose: To extract the hour from a time stamp.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Time Stamp | Formula | Result |
| 2 | 9:30 AM | =HOUR(A2) | 9 |
| 3 | 2:45 PM | =HOUR(A3) | 14 |
| 4 | 11:15 PM | =HOUR(A4) | 23 |
Explanation: This formula extracts the hour from the time stamp. For example, for the time stamp “2:45 PM”, the formula returns 14, representing the hour in a 24-hour format.
Example 2: Calculating the Duration of a Task
Purpose: To calculate the duration of a task in hours.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Time | End Time | Formula | Result |
| 2 | 9:00 AM | 11:00 AM | =HOUR(B2)-HOUR(A2) | 2 |
| 3 | 2:00 PM | 5:00 PM | =HOUR(B3)-HOUR(A3) | 3 |
| 4 | 8:00 AM | 4:00 PM | =HOUR(B4)-HOUR(A4) | 8 |
Explanation: This formula calculates the duration of a task by subtracting the start time from the end time.
Example 3: Determining the Shift of an Employee
Purpose: To determine an employee’s shift based on their start time.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Start Time | Formula | Result |
| 2 | 9:00 AM | =IF(HOUR(A2)<12, “Morning”, IF(HOUR(A2)<17, “Afternoon”, “Evening”)) | Morning |
| 3 | 2:00 PM | =IF(HOUR(A3)<12, “Morning”, IF(HOUR(A3)<17, “Afternoon”, “Evening”)) | Afternoon |
| 4 | 6:00 PM | =IF(HOUR(A4)<12, “Morning”, IF(HOUR(A4)<17, “Afternoon”, “Evening”)) | Evening |
Explanation: This formula determines an employee’s shift based on their start time. If the start time is before 12:00 PM, it returns “Morning”. If the start time is before 5:00 PM, it returns to “Afternoon”. Otherwise, it replaces “Evening”.
Example 4: Calculating the Time Elapsed Since the Start of the Day
Purpose: To calculate the time elapsed since the start of the day.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Current Time | Formula | Result |
| 2 | 9:30 AM | =HOUR(A2) | 9 |
| 3 | 2:45 PM | =HOUR(A3) | 14 |
| 4 | 11:15 PM | =HOUR(A4) | 23 |
Explanation: This formula calculates the time elapsed since the start of the day by returning the hour of the current time.
Example 5: Determining Whether It’s Morning, Afternoon, or Evening
Purpose: To determine whether it’s morning, afternoon, or evening based on the current time.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Current Time | Formula | Result |
| 2 | 9:30 AM | =IF(HOUR(A2)<12, “Morning”, IF(HOUR(A2)<17, “Afternoon”, “Evening”)) | Morning |
| 3 | 2:45 PM | =IF(HOUR(A3)<12, “Morning”, IF(HOUR(A3)<17, “Afternoon”, “Evening”)) | Afternoon |
| 4 | 11:15 PM | =IF(HOUR(A4)<12, “Morning”, IF(HOUR(A4)<17, “Afternoon”, “Evening”)) | Evening |
Explanation: This formula determines whether it’s morning, afternoon, or evening based on the current time. If the time is before 12:00 PM, it returns “Morning”. If the time is before 5:00 PM, it replaces “Afternoon”. Otherwise, it returns “Evening”.
Example 6: Using HOUR with IF to Check if it’s Time for Lunch
Purpose: To check if it’s time for lunch based on the current time.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Time | Formula | Result |
| 2 | 12:00 PM | =IF(HOUR(A2)=12, “Yes”, “No”) | Yes |
| 3 | 11:00 AM | =IF(HOUR(A3)=12, “Yes”, “No”) | No |
| 4 | 1:00 PM | =IF(HOUR(A4)=12, “Yes”, “No”) | No |
Explanation: This formula checks if the current time equals 12:00 PM (lunchtime). If it is, it returns “Yes”; otherwise, it replaces “No”.
Example 7: Using HOUR with SUM to Calculate Total Work Hours
Purpose: To calculate total work hours based on start and end times.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Start Time | End Time | Formula |
| 2 | 9:00 AM | 5:00 PM | =HOUR(B2)-HOUR(A2) |
| 3 | 8:00 AM | 4:00 PM | =HOUR(B3)-HOUR(A3) |
| 4 | 10:00 AM | 6:00 PM | =HOUR(B4)-HOUR(A4) |
Explanation: This formula calculates the total work hours by subtracting the start and end times.
Example 8: Using HOUR with VLOOKUP to Find Work Shifts
Purpose: To find the work shift for a given employee based on their start time.
Data tables and formulas:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Start Time | Shift | Formula | Result |
| 2 | John | 9:00 AM | Morning | =VLOOKUP(HOUR(A2),D2:E4,2,TRUE) | Morning |
| 3 | Jane | 1:00 PM | Afternoon | =VLOOKUP(HOUR(A3),D2:E4,2,TRUE) | Afternoon |
| 4 | Joe | 5:00 PM | Evening | =VLOOKUP(HOUR(A4),D2:E4,2,TRUE) | Evening |
Explanation: This formula uses VLOOKUP to find the work shift for a given employee based on their start time.
Example 9: Using HOUR with AVERAGE to Calculate Average Meeting Time
Purpose: To calculate the average meeting time based on the start times of several meetings.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Meeting 1 | Meeting 2 | Meeting 3 | Formula |
| 2 | 9:00 AM | 10:00 AM | 11:00 AM | =AVERAGE(HOUR(A2), HOUR(B2), HOUR(C2)) |
| 3 | 8:00 AM | 9:00 AM | 10:00 AM | =AVERAGE(HOUR(A3), HOUR(B3), HOUR(C3)) |
| 4 | 7:00 AM | 8:00 AM | 9:00 AM | =AVERAGE(HOUR(A4), HOUR(B4), HOUR(C4)) |
Explanation: This formula calculates the average meeting time by converting the meeting times to decimal values, averaging them, and then converting the result back to a time.
Example 10: Using HOUR with MAX to Find Latest Meeting Time
Purpose: To find the latest meeting time based on the start times of several meetings.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Meeting 1 | Meeting 2 | Meeting 3 | Formula |
| 2 | 9:00 AM | 10:00 AM | 11:00 AM | =MAX(HOUR(A2), HOUR(B2), HOUR(C2)) |
| 3 | 8:00 AM | 9:00 AM | 10:00 AM | =MAX(HOUR(A3), HOUR(B3), HOUR(C3)) |
| 4 | 7:00 AM | 8:00 AM | 9:00 AM | =MAX(HOUR(A4), HOUR(B4), HOUR(C4)) |
Explanation: This formula finds the latest meeting time by converting the meeting times to decimal values, finding the maximum value, and then converting the result back to a time.
Example 11: Using HOUR with MIN to Find the Earliest Meeting Time
Purpose: To find the earliest meeting time based on the start times of several meetings.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Meeting 1 | Meeting 2 | Meeting 3 | Formula |
| 2 | 9:00 AM | 10:00 AM | 11:00 AM | =MIN(HOUR(A2), HOUR(B2), HOUR(C2)) |
| 3 | 8:00 AM | 9:00 AM | 10:00 AM | =MIN(HOUR(A3), HOUR(B3), HOUR(C3)) |
| 4 | 7:00 AM | 8:00 AM | 9:00 AM | =MIN(HOUR(A4), HOUR(B4), HOUR(C4)) |
Explanation: This formula finds the earliest meeting time by converting the meeting times to decimal values, finding the minimum value, and then converting the result back to a time.
Example 12: Using HOUR with COUNTIF to Count Meetings in the Morning
Purpose: To count the number of meetings that start in the morning.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Meeting 1 | Meeting 2 | Meeting 3 | Formula |
| 2 | 9:00 AM | 10:00 AM | 11:00 AM | =COUNTIF(A2:C2, “<“&HOUR(“12:00 PM”)) |
| 3 | 8:00 AM | 1:00 PM | 2:00 PM | =COUNTIF(A3:C3, “<“&HOUR(“12:00 PM”)) |
| 4 | 7:00 AM | 2:00 PM | 3:00 PM | =COUNTIF(A4:C4, “<“&HOUR(“12:00 PM”)) |
Explanation: This formula counts the number of meetings that start before 12:00 PM (noon).
Example 13: Using HOUR with IFERROR to Handle Errors
Purpose: To handle errors when calculating the end time of a work shift.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Time | Shift Length | End Time | Formula |
| 2 | 9:00 AM | 8 | =IFERROR(HOUR(A2)+B2, “Error”) | |
| 3 | 8:00 AM | 9 | =IFERROR(HOUR(A3)+B3, “Error”) | |
| 4 | 10:00 AM | 10 | =IFERROR(HOUR(A4)+B4, “Error”) |
Explanation: This formula calculates the end time of a work shift. If the end time is later than 24 hours, it returns an error. The IFERROR function replaces this error with the text “Error”.
Part 3: Tips and Tricks
Use with Other Functions: The HOUR function can perform more complex calculations and analyses with other functions such as IF, SUM, and VLOOKUP.
Handling Errors: If the HOUR function returns an error, check to ensure that the time value is valid and correctly formatted.
24-Hour Format: Remember that the HOUR function returns the hour in a 24-hour format. If you need the hour in a 12-hour format, you may need to use additional functions or formatting.
Combining with MINUTE and SECOND: The HOUR function