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