登录查看更多内容

How to use the HOUR Function in Google Sheets

工程关注我们，每天学习??

?? Learning & Engineering

发布日期: 2024年4月2日

Returns the hour component of a specific time, in numeric format.

The HOUR function in Google Sheets is designed to extract the hour from a given time value. This function is essential for time data manipulation, enabling users to isolate the hour component for calculations, analysis, or formatting purposes. It's particularly useful in scheduling, logging, and any context where time tracking is crucial.

??Purchase our book to improve your Excel productivity

Benefits of Using the HOUR Function

Time Analysis Precision: Enhances the accuracy of time-based data analysis by isolating the hour component.
Simplified Scheduling and Logging: Facilitates the creation of schedules or logs by categorizing or filtering entries based on the hour.
Enhanced Data Visualization: Improves the visualization of time-related data by allowing for hour-based categorization in charts and graphs.
Dynamic Time Calculations: Supports dynamic calculations that depend on the hour of the day, aiding in automated workflows and conditional logic.

Master employee shift analysis with Google Sheets' HOUR function. Learn to calculate night shift hours accurately with our step-by-step tutorial and advanced tips.

Step-by-Step Guide

Step 1: Understanding the Syntax

HOUR(time)time: The time or cell reference from which you want to extract the hour.

Step 2: Preparing Your Data

Ensure your spreadsheet contains time data correctly formatted in Google Sheets. You might have timestamps from which you intend to extract the hour component.

Step 3: Applying the HOUR Function

Select the Output Cell: Click on the cell where you want the extracted hour to appear.
Input the Function: Enter =HOUR(, followed by the cell reference or direct time value you’re analyzing.

??Purchase our book to improve your Excel productivity

Example

Scenario: Night Shift Hours Analysis

Imagine you're an HR manager looking to analyze shift data for employees to identify who frequently works night shifts. You have data including employee names, shift start, and shift end times. Your goal is to calculate the total night shift hours worked by each employee within a given timeframe.

Data Setup:

- A1: Employee

- B1: Shift Start

- C1: Shift End

- D1: Night Shift Hours

- Rows 2-10: Contain the names of employees and their respective shift start and end times.

Objective:

Calculate the total hours each employee worked during the defined night shift hours.

Step-by-Step Implementation:

Step 1: Organize Your Spreadsheet

Fill out your spreadsheet with the relevant shift times for each employee.

Step 2: Calculate Night Shift Hours for Each Employee

To calculate night shift hours, you need to consider shifts that cross midnight and shifts that entirely fall within the night shift hours. For simplicity, let's focus on shifts that end after midnight but start before midnight:

1. D2: Enter the formula to calculate night shift hours. This formula will consider:

- Shifts ending after midnight.

- Shifts starting or ending within the night shift window.

Here's a basic approach to calculate hours worked between 10 PM (22 hours) and 6 AM (6 hours):

工程关注我们，每天学习?? 7 个月前

Rank if formula in Excel

工程关注我们，每天学习?? 6 个月前

How to Sum by week in Excel

工程关注我们，每天学习?? 8 个月前

=IF(AND(HOUR(B2)>=22, HOUR(C2)<=6), (HOUR(C2)+24)-HOUR(B2), IF(HOUR(B2)>=22, 24-HOUR(B2), IF(AND(HOUR(C2)<=6, HOUR(C2)>0), HOUR(C2), 0)))

This formula checks:

- If the shift starts after 10 PM and ends before 6 AM, calculating hours worked during the night shift.

- If the shift starts after 10 PM but doesn't cross midnight, calculating until midnight.

- If the shift ends before 6 AM but doesn't start before midnight, calculating from midnight.

2. Adjust for Midnight Crossings: Shifts that cross midnight require special consideration, as simply subtracting start time from end time won't accurately reflect hours worked during the night shift window.

Advanced Tips

Combining with Other Time Functions: Use HOUR in conjunction with MINUTE and SECOND for detailed time calculations that need to break down the entire timestamp.
Dynamic Time Ranges: For analyses that span specific hours (e.g., night shifts), dynamically calculate time ranges using HOUR within conditional statements or array formulas.
Error Handling: Incorporate IFERROR with your HOUR function to gracefully handle any errors, especially when dealing with a mix of time formats or non-time values.
Custom Formatting for Display: While HOUR extracts the hour as a number, remember you can use custom formatting to display this number as part of a time (e.g., hh AM/PM) if reintegrating it into a time-based context.