How to Use the HOUR Function in Excel
Today, let’s explore the concept of time manipulation in Excel and Google Sheets with an emphasis on the HOUR function. This function is essential for extracting the hour part from a time value in a cell. It proves extremely useful for conducting calculations or analyses that depend on the hour component of a timestamp.
Syntax
The syntax for the HOUR function is consistent across both Excel and Google Sheets:
=HOUR(serial_number)serial_number– This is the time value from which you wish to extract the hour. It could be a cell reference that includes a date and time, a function that outputs a date and time, or a numerical value that Excel interprets as a date and time.
Examples
Example 1: Basic Usage
Consider a scenario where cell A1 contains the timestamp 9/25/2023 14:30:00. To extract the hour component from this timestamp, use the formula:
| Data | Formula | Result |
|---|---|---|
| 9/25/2023 14:30:00 | =HOUR(A1) | 14 |
Example 2: Calculating Overtime Pay
Suppose you have a list of employee clock-in times in column A and need to compute the total overtime hours worked. By combining the HOUR function with other functions, you can accomplish this task. If the standard workday is 8 hours, use the following formula to calculate overtime hours:
| Clock-in Time | Overtime Calculation |
|---|---|
| 8:30:00 | =MAX(0, HOUR(A2)-8) |
This formula deducts 8 hours (regular work hours) from the hour extracted in cell A2. If the result is negative, it indicates that no overtime was worked, and the MAX function ensures that the displayed value does not drop below zero.
Conclusion
Both Excel and Google Sheets offer the HOUR function as a straightforward and effective tool for extracting the hour part from a timestamp, facilitating various calculations and analyses. Whether it’s tracking work hours, crunching time-based data, or carrying out any time-sensitive tasks, mastering the use of the HOUR function can dramatically enhance your productivity.
More information: https://support.microsoft.com/en-us/office/hour-function-a3afa879-86cb-4339-b1b5-2dd2d7310ac7