Payroll hours time calculation is important when you want to perform overtime hours calculation of long list of employees. It will be time taking to manually add the overtime hours worked by employees, if the number of employees are large.
I used the Google Sheets time calculation for calculating the total hours worked of hired heavy equipment in my tenure with my employer. You can use a simple Google Sheets time formula to sum hours and minutes.
So let us learn how to use Google Sheets time functions to add hours and minutes for your payroll as well as similar time calculations.
We are going to use two Google Sheets time functions in this payroll time calculation. They are MINUTE and HOUR. If you are doing lots of time related calculations, I suggest you to check my Google Sheets complete time functions tutorial which can be used as a reference sheet.
Similar: Google Sheets Complete Date Functions.
So back to hour payroll hours calculation in Google Sheets. You can follow this tutorial to use your spreadsheet as payroll hours calculator or time sheet calculator.
Payroll Hours Time Calculation in Google Sheets
- Time should be in 24 format like 17:30:00. If it’s not, you can set it from the Format -> Number.
- We only calculate hours and minutes no seconds. Take care while entering the time.
Enter the below sample data in a new Google Spreadsheet to sum hours and minute.
With a one line array formula you can get the sum of hours and minutes worked. Before coming to that you should know the simple alternative method by only using the time formulas.
Here I am explaining the highlighted part of the formula. The same you can copy paste down and use the sum function to find total hours worked.
Explanation to the highlighted part.
What we did here in cell E2?
See the below formula in E2.
The HOUR formula returns the hour component from a given time. So here we used it to find the difference of hours between a start and end time and the result will be of course in hours.
Similarly we should calculate the minute difference.
Check the formula in cell F2. What we did here?
The MINUTE formula returns the minute component of a given time. Here we find the minute difference between two given time. This function only consider the minutes components of the start and end time. Hours will be excluded. Then you should convert minutes to hour like below so that we can add this with the hour difference we already got using the first formula.
So we have the hours difference in hours and minutes difference also in hours. Just add it. We did the same in cell H2 in the example.
You can use an array formula to add all the above calculations in one line. Apply the below formula in cell I2.
The whole process in the Payroll Hours Time Calculation is actually simple. But you should create a sample data same as above and apply the formulas yourself to understand it. Just reading the above tutorial is not sufficient.
Hope to see you again here with another useful Google Sheets tutorial.