What Is The HOUR Function in Google Sheets?
The HOUR function in Google Sheets is used to extract the hour component when supplied with a time value. The output of the formula is a number between 0 and 23. These values correspond to the hour in a 24-hour clock format. It is useful when working with time-based data if you wish to isolate the hour to perform further calculations, such as calculating work shifts, identifying peak traffic hours, or filtering records by time of day.
For example, suppose you have a time value of 6:45 PM in cell A1. Let us enter the formula =HOUR(A1) in an empty cell and press Enter. We get a result of 18. It represents 6 PM in in a 24-hour format. This function is very helpful for scheduling, calculating deliveries/sales within a time period, and in time tracking. It is also useful when we have a team across different time zones.
Key Takeaways
- The HOUR function extracts the hour part of a supplied time or a date-time value. It returns a value between 0 and 23, representing the hour in 24-hour format.
- The syntax of HOUR in Google Sheets is as follows: =HOUR(time)
time: This should be a valid time, date-time, serial number, or a cell reference containing a time value.
- The HOUR function helps track work shifts, assign tasks, or create hour-wise reports.
- We can combine it with functions like IF, SUMIF, ARRAYFORMULA, or TEXT to perform calculations or display results in custom time formats.
Syntax
The syntax of the HOUR function in Google Sheets is as follows:
=HOUR(time)
- time: the input time in either time or date-time format from which the hour will be extracted. It can be a direct value or a cell reference.
Thus, the HOUR in Google Sheets formula is very simple.
How To Use HOUR Function in Google Sheets?
The HOUR in Google Sheets is an extremely versatile function for extracting the hour from any time. To use it in Google Sheets, there are a few ways we can implement them.
- Manually entering HOUR
- Insert through the menu bar.
Entering The HOUR Function Manually
The HOUR function in Google Sheets is used to extract the hour from a time value specified as an argument. Let us go through the steps using a simple example to understand how to implement it.
Step 1: Let us first enter the details in a spreadsheet. Here, we enter a complete time value, for instance, 3:45:20 PM in cell A1. This value shows the full time from which we must extract the hour portion.
Step 2: Click on a cell wish to see the result. Here, we click on A2. Type the following formula:
=HOUR(
Step 3: Now, input the cell reference that holds the time value. We enter the following formula in an empty cell.
=HOUR(A1)
Step 4: Press Enter. We get the result as 15. The resultant value represents 3 PM in a 24-hour format. It is a very helpful function that is straightforward and not time-consuming to understand.
Using the Google Menu Bar
1: Click on the cell where you wish to see the result.
2: Go to Insert -> Function -> Date -> HOUR.
3: Type in the argument, a cell reference, or a time value, close the parentheses and press Enter.
Examples
The HOUR function is used to obtain the hour component from a time value. It is ideal for analyzing time-based data and can be used in different scenarios.
Let’s explore a few practical examples of how the HOUR function can be used in Google Sheets to extract the hour from timestamps.
Example #1
In this example, a global team works on a project. Hence, keeping track of local and remote time zones becomes essential. Google Sheets does not have any built-in time zone-related functions. However, one can calculate time differences and extract the hour using the HOUR function. Suppose there’s a meeting in London at 1 pm, let us find the hour in Tokyo to log in for the same.
Remember, Tokyo is 9 hours ahead of London. Let us adjust for the time difference and extract the hour.
Step 1: Enter the TIME function to find the London time as follows. We mention 13 to represent 1 PM.
=TIME(13, 0, 0)
Enter this in cell A1.
Step 2: Now, let us add the time zone difference with the TIME function to the original London time in A1.
Add the following formula in cell B1.
=A1 + TIME(9, 0, 0)
This gives us Tokyo’s local time.
Step 3: Let us try to extract the hour in Tokyo scheduled for the meeting. Use the HOUR function as follows:
=HOUR(B1)
We get a result of 22, representing 10 PM in Tokyo.
This method helps us adjust and track meeting hours across different time zones using the HOUR function.
Example #2 – Combining the HOUR Function with IF Function
In this HOUR in Google Sheets example, we consider the scenario at a customer support center. We have a log of calls received by them. The center must check if the calls are answered during business hours between 9 AM and 7 PM. The calls are then classified as “Within Business Hours” or “Outside Business Hours.” Based on the time in the call log. Let us combine the HOUR in Google Sheets with the IF function to perform this task.
Step 1: Enter the call times in Column A.
Step 2: Use the following formula to find whether the call was within the working hours between 9 AM to 7 PM with the following formula in cell B2.
=IF(AND(HOUR(A2)>=9, HOUR(A2)<19), “Within Business Hours”, “Outside Business Hours”)
- Here, the AND function checks for two conditions that should be satisfied. The value in A2 should be between 9 representing 9 AM and 19, representing 7 PM.
- If the conditions are satisfied, the formula prints: “Within Business Hours.”
- If either or both conditions are not satisfied, the formula prints: “Outside Business Hours.”
Step 3: Press Enter. For cell A2, we get the result as “Outside Business Hours” since 6:17 AM is before 9 AM.
Step 4: Use the fill handle and apply the formula to the rest of the values.
This method helps in monitoring if the calls come during business hours without the need for any checking which must be done manually.
Example #3 – Combining the HOUR Function with SUMIF Function
A man runs a bouquet delivery service and wants to calculate the total number of bouquets delivered before 1 PM. He has a list of the delivery times and the corresponding bouquet counts. He must combine the HOUR and SUMIF functions for the total deliveries made within specific hours.
Step 1: Let us enter the delivery times in Column A and the number of packages in Column B.
Step 2: Now, enter the following formula in cell C2.
=HOUR(A2)
Drag it down for the rest of the rows.
Step 3: To calculate the total packages delivered before 1 PM, we use the below formula:
=SUMIF(C2:C10, “<13”, B2:B10)
Step 4: Press Enter. The formula will sum package counts delivered before 1 PM.
This calculation with HOUR and SUMIF helps analyze the delivery performance by hours without manually filtering the time values.
Important Things to Note
- The HOUR function always returns the hour in a 24-hour format. The values are from 0 to 23.
- The HOUR in Google Sheets works with time and datetime values only; it does not work with text that does not represent time.
- If the input time is invalid or not formatted, the function will return an error.
- The function ignores the date and uses only the hour part when we use the Date-Time value.
Frequently Asked Questions (FAQs)
The HOUR function works with a date and time value. For example, if a cell contains a date time value as: 02/06/2024 15:45:00, we can use the formula =HOUR(A1) to return the hour. The formula returns 15, which corresponds to the hour value of the date-time. It ignores the date part and only extracts the hour.
In real-time scenarios, it can help in maintaining staffing by determining when employees arrive and leave. For delivery services, you can manage deliveries by finding out when they are made and can analyze if there are any peak times for deliveries. In a similar way, you can also analyze the class attendance, checking for the highest attendance to schedule classes.
There are a host of other uses such as calculating the right time when teams are across time zones, etc.
If you must add less than 24 hours, you must just use the following formula. For instance, to add 5 hours to a time, we use, =A1+TIME(5,0,0). If you wish to add more than 24 hours, you must use the formula, Time + ( hours/24). For instance, A1 + (28/24).
In a similar way, you can subtract hours from a time value. To subtract, change the plus sign to a minus sign.
=A1-TIME(5,0,0) – to subtract five hours
=A1-(28/24) – to subtract 28 hours
Download Template
This article must help understand HOUR Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is HOUR Function in Google Sheets. We learn how to use it to extract the hour component from a time value with examples. You can learn more from the following articles. –
Leave a Reply