Now we can use the same arguments with the MINUTE function as below.įormula 2 MINUTE(C2:C11-B2:B11+E2:E11-D2:D11)/60 Now the balance left is MINUTES.Ībove, in the calculation, we have used the HOUR function. Then adds that value (Hrs.) with the total hours from lunch break end time to duty end time. After Deducting Lunch Hours = Lunch Break Start – Duty Start Time + Duty End Time – Lunch Break End.įirst, this formula calculates the total hours from duty start time to lunch break. We can use the HOUR function in Google Sheets to return the hours from time.Īctually, the formula calculation is like this. The above formula to find the total working hours excluding lunch hour is a combination formula with two parts.įormula 1 HOUR(C2:C11-B2:B11+E2:E11-D2:D11) I’ll come to that after the above formula explanation.
I have a dynamic formula that works irrespective of in and out time. It only applies when employees punch in before lunch break begins and punch out after lunch break ends. Here is the formula I’ve used in the above example in cell F2 for working hours calculation after deducting the lunchtime.
Similar: Payroll Hours Time Calculation in Google Sheets Using Time Functions Basic Formula to Deduct Lunch Break Time From Total Hours
#Calculate timesheet with lunch how to#
Now it’s time to explain how to deduct lunch break time from total hours. If you don’t know how to use HOUR and MINUTE functions in Google Sheets, you can check my Functions Guide. One is the HOUR, and the other is the MINUTE. In addition to the ArrayFormula, I’m only using two Time functions in this Google Sheets tutorial.
0 kommentar(er)
