We’ll be learning how to calculate hours worked in Google Sheets today. Whether you need to find out how many hours your employee worked or execute any other mathematical operation, Google Sheets is a calculating engine. Finding hours worked is effective with Google Sheets; all you have to do is enter values. For further information, see the following tutorial on how to calculate hours worked in Google Sheets.
Why is it Important to Calculate Hours Worked?
Payroll Accuracy: Precise hourly labor calculations guarantee that workers receive just compensation, preventing payroll errors and any legal problems.
Compliance: Working hours, overtime, and breaks are governed by a number of labor laws and regulations. Organizations can avoid fines and legal repercussions by staying in compliance with these requirements through the calculation of hours worked.
Resource Allocation: Businesses can allocate resources more effectively when they are aware of the hours that employees spend on various tasks. This knowledge aids in the optimization of staffing levels, guaranteeing that workloads are tolerable and tasks are accomplished effectively.
Monitoring of Productivity: An indicator of worker productivity is the number of hours worked. It aids in pattern recognition, workload distribution analysis, and the removal of workflow bottlenecks and inefficiencies.
You may also like>>> How To Make Categories In Google Sheets [Easy Guide]
How to Calculate Hours Worked in Google Sheets
We are going to learn two different methods in the following tutorial.
- Calculate hours worked Using simple subtraction
- Calculate hours worked Using the MINUS formula
Calculate hours worked By Using simple subtraction
To determine the employee or participant’s total working hours using this method, we simply subtract the Time Out from the Time In.
Step 1>
As you can see in the example below, we need to identify the hours that certain workers worked in Google Sheets because we have information on their time in and time out. First, point the mouse where you want the hours worked to be calculated.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-1.png)
Step 2>
Once the cell where the working hours are to be calculated has been found, use an equal sign to execute the formula and enter the cell reference for Time Out first, as I have done in the example below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-2.png)
Step 3>
In order to calculate the number of hours worked in Google Sheets, enter the cell reference for Time Out, then subtract one using the keyboard.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-3.png)
Step 4>
When the formula is complete, hit Enter to see the result, then drag it over other cells to see the results of those other cells. But what do we see? While calculating work hours, we discovered several complex values in decimal points, as shown in the following image.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-4.png)
Step 5>
This is typically the result of Google Sheets’ cell format when we remove time, but it’s not a cause for concern. You only need to multiply each of the resulting values by 24 to convert these decimal values into decimal time.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-5.png)
Step 6>
As you can see, we were capable of determining the employees’ worked hours based on their time in and time out. The result is shown in the figure below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-6.png)
Calculate hours worked Using the MINUS formula
You are able to calculate the working hours using the MINUS method if you know when to start and stop working. The procedures to use the MINUS algorithm to find hours worked are listed below.
Step 1>
In Google Sheets, first position your cursor where you wish to calculate the number of hours worked.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-11.png)
Step 2>
To use the subtract formula in Google Sheets, point to “Minus” and then push the equal sign, just like I have in the screenshot below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-12.png)
Step 3>
Give the cell reference for Time Out first when you begin the minus formula since we need to subtract Time Out from Time In in order to find worked hours.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-13.png)
Step 4>
Provide the Time Out cell reference first, then the Time In cell reference, and then close the bracket. Remember to use commas to separate these parameters, as seen in the example below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-14.png)
Step 5>
You can now obtain the result by just pressing the Enter key or by dragging this formula over the other cells. In Google Sheets, when we subtract time, the result can show up as decimal numbers or in time format. Even though the time result is accurate, the distinction between AM and PM could be confusing, as seen below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-15.png)
Step 6>
While calculating the working hours, you can turn the resulting values into a format representing duration. Hovering the cursor over each cell will select them all before formatting.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-16.png)
Step 7>
Once the cells have been selected, locate the “Format” tab in Google Sheets’ menu bar, as indicated below. To format your cells, click on it to open.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-17.png)
Step 8>
A drop-down menu will appear when you select the “Format” tab from Google Sheets’ menu bar, and the “Number” option will be visible as it is noted below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-18.png)
Step 9>
Upon selecting the “Number” option, a new menu will appear, displaying various cell format options. Choose the “Duration” format from the list, just like I have in the image below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-19.png)
Step 10>
As you can see in the following image, you have now received the actual results for working hours. You can calculate the worked hours in Google Sheets in this straightforward manner.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-20.png)
You may also like>>> How To Remove Hyperlinks In Google Sheets [4 Multiple Methods]
Frequently Asked Questions
What happens if my work schedule is flexible and my hourly rate varies?
The calculations can be modified to account for different hourly rates. For hourly rates, add a separate column and modify the calculation formula such that the applicable rate is multiplied by the number of hours worked.
How can I use Google Sheets to determine my hours worked without taking breaks?
Because most brokers pay their staff on an hourly basis, they also don’t include breaks in their working hours. If you have a task like this, the method for calculating hours worked in Google Sheets without breaks is provided below.
Step 1>
We have attendance time for multiple employees, including break time, as you can see in the sample below. We now need to determine their working hours, which do not include breaks. Move the pointer to the desired location in Google Sheets to view your result.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-21.png)
Step 2>
Here, we’ll use the formula to determine each employee’s working hours, excluding breaks. First, we will subtract the Time Out from the Time In in our syntax by providing the cell reference in the manner shown below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-22.png)
Step 3>
Subtract the break time from the values of Time Out and Time In in order to exclude it as well. Once more, add a minus sign to your syntax and indicate the break time’s cell reference as I have indicated in the image below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-23.png)
Step 4>
Nothing else needs to be done; simply hit the Enter key to see the results. As the image below shows, working hours, excluding breaks, are displayed.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-24.png)
How to calculate O.T. hours in Google Sheets?
People frequently work longer hours than the required hours in most organizations, but they are compensated for them. However, how are their overtime or O.T. hours known to you? The steps to locate O.T. hours are listed below.
Step 1>
Working and normal hours are needed to calculate O.T. hours. As you can see in the example below, we have both, so let’s get started with the O.T. hour calculation in Google Sheets.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-31.png)
Step 2>
Here, we’ll calculate the O.T. hours in Google Sheets using the “IF” function. The logical statement that we have created (If working hours are larger than ordinary hours) in the following example must first be given by the syntax of the IF function. (We will count standard working hours 6:00).
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-32.png)
Remember to absolute the cell reference in order to correct the cell reference during regular business hours and avoid mistakes.
Step 3>
Now, in accordance with the syntax, we must indicate by providing their cell reference whether the value where we have requested to “Subtract working hours from regular hours” is true.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-33.png)
Step 4>
In the same way, set any value that is false to”0“, as I have indicated in the code below.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-34.png)
Step 5>
As can be seen in the result in the next image, you will now receive a result that, if the value of working hours is larger than standard regular hours, it will automatically remove it when calculating the O.T hours; otherwise, it will remain zero.
![How to Calculate Hours Worked in Google Sheets How to Calculate Hours Worked in Google Sheets](https://officechaser.com/wp-content/uploads/2023/12/How-to-Calculate-Hours-Worked-in-Google-Sheets-35.png)
Conclusion
This article gave guidance on how to calculate hours worked in Google Sheets. Knowing this makes it easy to modify the formulas to suit your requirements. Use Google Sheets to streamline your time-tracking procedure, and don’t hesitate to ask any last-minute queries to read the FAQs.