In this quick tutorial, let’s learn how to deduct lunch break time from total hours in Google Sheets.
Time calculation has significance in preparing payroll, salary slips of employees, equipment rental, etc.
With limited exposure to Time functions, you can do such calculations in Google Sheets.
In this post, let’s learn how to deduct lunch break time from total hours. You may find the formula useful in your day-to-day life.
To improve the formula, I may use ArrayFormula with the time calculations.
I am sharing a time formula that you can use with the data range where the time is entered in 12 or 24 hours format.
The former time format is without leading zero and followed by AM/PM, whereas the latter is with leading zero (no AM/PM).
So to make this clearer to you, I’ve formatted part of my sample data into 12 hrs.
You May Like: How to Compare Time Stamp with Normal Date in Google Sheets
Sample Data
Below is my sample data for the basic formula example.
In addition to the ArrayFormula, I’m only using two Time functions in this Google Sheets tutorial.
One is the HOUR, and the other is the MINUTE.
If you don’t know how to use HOUR and MINUTE functions in Google Sheets, you can check my Functions Guide.
Now it’s time to explain how to deduct lunch break time from total hours.
Similar: Payroll Hours Time Calculation in Google Sheets Using Time Functions
Basic Formula to Deduct Lunch Break Time From Total Hours
Master Formula
Here is the formula I’ve used in the above example in cell F2 for working hours calculation after deducting the lunchtime.
=ArrayFormula((HOUR(C2:C11-B2:B11+E2:E11-D2:D11))+(MINUTE(C2:C11-B2:B11+E2:E11-D2:D11)/60))
It only applies when employees punch in before lunch break begins and punch out after lunch break ends.
I have a dynamic formula that works irrespective of in and out time. I’ll come to that after the above formula explanation.
Formula Explanation
The above formula to find the total working hours excluding lunch hour is a combination formula with two parts.
They are as follows.
Formula 1
HOUR(C2:C11-B2:B11+E2:E11-D2:D11)
We can use the HOUR function in Google Sheets to return the hours from time.
Actually, the formula calculation is like this.
Working Hrs. After Deducting Lunch Hours = Lunch Break Start – Duty Start Time + Duty End Time – Lunch Break End.
First, this formula calculates the total hours from duty start time to lunch break.
Then adds that value (Hrs.) with the total hours from lunch break end time to duty end time.
So we have got the total hours worked. Now the balance left is MINUTES.
Above, in the calculation, we have used the HOUR function.
Now we can use the same arguments with the MINUTE function as below.
Formula 2
MINUTE(C2:C11-B2:B11+E2:E11-D2:D11)/60
The only difference here is the MINUTE function used instead of the HOUR function.
But please note the returned value is in minutes, not in hours.
So I’ve converted this value to hours by dividing it by 60.
The final formula is the combination of this formula 1 and formula 2. But for an expanded result, I’ve used ArrayFormula.
Advanced Formula to Calculate Hours Worked and Minus Lunch Break Time
The above formula may not work in the following cases.
- Employees punch out before or during the lunch break.
- Punch in during or after the lunch break.
- Punch in and punch out during lunch break.
Such scenarios are not common but can happen in real-life. So what’s the solution?
Time to use a few IF logical tests.
We will generate work start and end times for the calculation after dynamically adjusting the lunch breaks.
So, we can easily deduct lunch break time from total hours.
Generic Formula:
=ArrayFormula((hour(dynamic_work_end_time)-hour(dynamic_work_start_time))+(minute(dynamic_work_end_time)-minute(dynamic_work_start_time))/60)
dynamic_work_start_time:
=ArrayFormula(if(B2:B11<=C2:C11,B2:B11,if(B2:B11<=D2:D11,C2:C11,B2:B11-(D2:D11-C2:C11))))
dynamic_work_end_time:
=ArrayFormula(if(E2:E11<=C2:C11,E2:E11,if(E2:E11<=D2:D11,C2:C11,E2:E11-(D2:D11-C2:C11))))
Advanced Formula to Calculate Hours Worked and Minus Lunch Break Time (F2):
=ArrayFormula((hour(if(E2:E11<=C2:C11,E2:E11,if(E2:E11<=D2:D11,C2:C11,E2:E11-(D2:D11-C2:C11))))-hour(if(B2:B11<=C2:C11,B2:B11,if(B2:B11<=D2:D11,C2:C11,B2:B11-(D2:D11-C2:C11)))))+(minute(if(E2:E11<=C2:C11,E2:E11,if(E2:E11<=D2:D11,C2:C11,E2:E11-(D2:D11-C2:C11))))-minute(if(B2:B11<=C2:C11,B2:B11,if(B2:B11<=D2:D11,C2:C11,B2:B11-(D2:D11-C2:C11)))))/60)
This way, we can dynamically deduct lunch break time from total hours in Google Sheets.
I hope you have liked it.
For any clarification, please feel free to use the comments form below.
Related Articles:
1. Google Sheets: Split Night Shift Job Hours into Two Columns.
Hi Prashanth,
Thank you so much for your help. I really appreciate it.
But I noticed in “Total Hours” is not accurate?
In row 2, your total hours are 3.50. Shouldn’t it be only 3.30?
In row 5, Shouldn’t it be 0.15 instead of 0.25?
I don’t know how to thank you. It really helps me a lot. Cheers! 🙂
Hi, Jay-ar,
That is correct in terms of multiplying with amounts.
If the hire amount is 100 per hour, for 3 1/2 hours, it will be 3.50*100, not 3.30*100.
If you are so particular to convert, use the below formula in F2 and apply Format > Number > Duration.
=ARRAYFORMULA(F2:F11/24)
Similar:- Convert Decimals to Minutes and Minutes to Decimals in Google Sheets.
Hi Prashanth,
Thank you for your reply.
Sorry for the inconvenience and the unclear question.
D9 – Name
E9 – Time-In
F9 – Time-Out
G9 – Total Hours of Worked
H9 and I9 are the start and end of the break.
Our working hours are from 8 am to 5 pm.
The question is, if the employee timed in at 8 am and left at 11 am, his total working hours should be “3 hours”. But the total hours of work was only 2 hours.
I appreciate the time and help you may extend in my questions.
Thank you.
Hi, Jay-ar,
Thanks for the clarification.
I have updated the post and also included a sample sheet.
I hope that helps?
Hi, Good day!
Your codes are very useful to me, and I am using this for my company employees.
I used your codes above, but I have noticed one problem.
If the employee is time out before noon, the total hours have automatically 1 hour less.
Can you fix this?
Thank you in advance.
Hi, Jay-ar,
I’m not clear. Can you share an example sheet in your reply below?
Hi,
I do have the Planned start time and I need to give planned end time by excluding break hours, it means in a day we do have two shifts and each shift has two breaks. Here manually we are calculating to exclude 1 hour of break, in that particular time slot.
Hi, Hima,
My access to your Sheet was denied!