Payroll hours time calculation is important when you want to perform overtime hours calculation of your long list of employees. It will be time taking to manually add the overtime hours worked by employees if the number of employees is large.

During my tenure with my past employer, I used Google Sheets time calculation so many times for calculating the overtime hours worked by the operator of our hired equipment.

You can use a simple Google Sheets time formula to sum hours and minutes. So let me explain how to use Google Sheets time functions to add hours and minutes in your payroll as well as in other similar time calculations.

I am 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 check my Google Sheets complete time functions tutorial as a reference guide.

Similar: Google Sheets Complete Date Functions.

Back to our payroll hours calculation in Google Sheets. You can follow this tutorial to use your Spreadsheet as a payroll hours calculator or time sheet calculator.

Steps: Payroll Hours Time Calculation in Google Sheets

Important:

• Time should be in 24 hr formats like 17:30:00. If it’s not, you can set it from the Format -> Number. This is to just avoid error. But 12-hour format will also work.
• I only considered the hours and minutes in this calculation. I have excluded seconds as it’s not important in payroll. So please take care while entering the time inputs.

Enter the below sample data in a new Google Spreadsheet to sum hours and minutes worked (total of time difference).

With a one-line array formula, you can get the sum of hours and minutes worked. I will come to that later.

First, we can use individual formulas. It will help you to understand the functions Hour and Minute.

Non-Array Formula to Calculate Payroll Hours Time Difference

Example:

Here I am explaining the highlighted part of the formula. The same you can copy to the cells down and use the sum function to find the total hours worked.

Formula Explanation

Step 1: Formula in E2.

First let me go into the detail of the formula in cell E2.

=hour(D2-C2)

Drag this formula down until cell E11.

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 time and end time. The result will be of course in hours.

Step 2: Formula in F2.

Similarly, we can calculate the minute difference. Here is the formula in cell F2 and explanation.

=MINUTE(D2-C2)

Drag this formula down until cell F11.

The MINUTE formula returns the minute component of a given time. Here the above formula returns the difference between minutes from the start and end time.

Please note that the Minute formula only considers the minute components of the start and end time. Hours will be excluded.

Step 3: Formula in G2.

Converts minutes difference that we have just got in the above step to hours. So that we can add this later to the hours we got in Step 1.

=F2/60

Drag this formula down until cell F11.

Step 4: Formula in H2.

So we have the hours difference in hours and minutes difference also in hours. Just add these values. You can see the same in cell H2.

=E2+G2

Drag this formula down until cell H11.

Array Formula to Calculate Payroll Time Difference and Total

You can use an array formula to add all the above calculations in one line. Apply the below formula in cell I2. No need to copy/drag this formula down.

=ARRAYFORMULA((HOUR(D2:D11-C2:C11))+(MINUTE((D2:D11-C2:C11)))/60)

As you can see in I12 I have used the SUM function to total the output of the Array Formula.

=sum(I2:I11)

The whole process in the Payroll Hours Time Calculation is actually simple. Just wrap the above array formula with sum!

You must create a sample data same as above and apply the formulas yourself to understand it. Just reading the above tutorial won’t be sufficient to master it. Hope to see you again with another useful Google Sheets tutorial.

Additional Resources:

1. How to Deduct Lunch Break Time From Total Hours in Google Sheets.
2. Google Sheets: The Best Overtime Calculation Formula.
3. How to Convert Military Time in Google Sheets.
4. Move Time In and Time Out to the Same Row in Google Sheets.
5. Elapsed Days and Time Between Two Dates in Google Sheets.
6. How to Add Hours, Minutes, Seconds to Time in Google Sheets.
7. How to Format Time to Millisecond Format in Google Sheets.