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

syntax
```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:

ABC
1Time StampFormulaResult
29:30 AM=HOUR(A2)9
32:45 PM=HOUR(A3)14
411: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:

ABCD
1Start TimeEnd TimeFormulaResult
29:00 AM11:00 AM=HOUR(B2)-HOUR(A2)2
32:00 PM5:00 PM=HOUR(B3)-HOUR(A3)3
48:00 AM4: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:

ABC
1Start TimeFormulaResult
29:00 AM=IF(HOUR(A2)<12, “Morning”, IF(HOUR(A2)<17, “Afternoon”, “Evening”))Morning
32:00 PM=IF(HOUR(A3)<12, “Morning”, IF(HOUR(A3)<17, “Afternoon”, “Evening”))Afternoon
46: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:

ABC
1Current TimeFormulaResult
29:30 AM=HOUR(A2)9
32:45 PM=HOUR(A3)14
411: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:

ABC
1Current TimeFormulaResult
29:30 AM=IF(HOUR(A2)<12, “Morning”, IF(HOUR(A2)<17, “Afternoon”, “Evening”))Morning
32:45 PM=IF(HOUR(A3)<12, “Morning”, IF(HOUR(A3)<17, “Afternoon”, “Evening”))Afternoon
411: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:

ABC
1TimeFormulaResult
212:00 PM=IF(HOUR(A2)=12, “Yes”, “No”)Yes
311:00 AM=IF(HOUR(A3)=12, “Yes”, “No”)No
41: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:

ABC
1Start TimeEnd TimeFormula
29:00 AM5:00 PM=HOUR(B2)-HOUR(A2)
38:00 AM4:00 PM=HOUR(B3)-HOUR(A3)
410:00 AM6: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:

ABCDE
1EmployeeStart TimeShiftFormulaResult
2John9:00 AMMorning=VLOOKUP(HOUR(A2),D2:E4,2,TRUE)Morning
3Jane1:00 PMAfternoon=VLOOKUP(HOUR(A3),D2:E4,2,TRUE)Afternoon
4Joe5:00 PMEvening=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:

ABCD
1Meeting 1Meeting 2Meeting 3Formula
29:00 AM10:00 AM11:00 AM=AVERAGE(HOUR(A2), HOUR(B2), HOUR(C2))
38:00 AM9:00 AM10:00 AM=AVERAGE(HOUR(A3), HOUR(B3), HOUR(C3))
47:00 AM8:00 AM9: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:

ABCD
1Meeting 1Meeting 2Meeting 3Formula
29:00 AM10:00 AM11:00 AM=MAX(HOUR(A2), HOUR(B2), HOUR(C2))
38:00 AM9:00 AM10:00 AM=MAX(HOUR(A3), HOUR(B3), HOUR(C3))
47:00 AM8:00 AM9: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:

ABCD
1Meeting 1Meeting 2Meeting 3Formula
29:00 AM10:00 AM11:00 AM=MIN(HOUR(A2), HOUR(B2), HOUR(C2))
38:00 AM9:00 AM10:00 AM=MIN(HOUR(A3), HOUR(B3), HOUR(C3))
47:00 AM8:00 AM9: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:

ABCD
1Meeting 1Meeting 2Meeting 3Formula
29:00 AM10:00 AM11:00 AM=COUNTIF(A2:C2, “<“&HOUR(“12:00 PM”))
38:00 AM1:00 PM2:00 PM=COUNTIF(A3:C3, “<“&HOUR(“12:00 PM”))
47:00 AM2:00 PM3: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:

ABCD
1Start TimeShift LengthEnd TimeFormula
29:00 AM8=IFERROR(HOUR(A2)+B2, “Error”)
38:00 AM9=IFERROR(HOUR(A3)+B3, “Error”)
410:00 AM10=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

1. Use with Other Functions: The HOUR function can perform more complex calculations and analyses with other functions such as IF, SUM, and VLOOKUP.

2. Handling Errors: If the HOUR function returns an error, check to ensure that the time value is valid and correctly formatted.

3. 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.

4. Combining with MINUTE and SECOND: The HOUR function