When deducting lunch break time from total working hours in Google Sheets, consider these points:
- An employee might work a half-day, either leaving before lunch or arriving after lunch.
- Lunch break times may vary by shift.
- Lunch break practices vary globally, ranging from 30 minutes to 3 hours.
To account for these aspects when coding an automatic lunch break deduction formula, we’ll use a table structure that includes start time, lunch break start, lunch break end, and end time. This structure can handle all these considerations.
I highly suggest using datetime instead of time if your working hours start on one day and end on another, spanning two days.
Automatically Deduct Lunch Breaks in Google Sheets
We will use the MAP function as part of an array formula to deduct lunch break time from total working hours in Google Sheets.
The sample data is in the range A1:F9, where the field labels in A1:F1 are “Employee Name,” “Start Time,” “Lunch Break Start,” “Lunch Break End,” “End Time”, and “Total Working Hours.”
The formula will be in cell F2, which will return the total working hours after deducting the lunch break time.
Formula:
=MAP(
B2:B9, C2:C9, D2:D9, E2:E9,
LAMBDA(s, lbs, lbe, e,
((e-s)-(MAX(lbs, MIN(lbe, e))-MIN(MAX(s, lbs), lbe)))*24
)
)
Select F2:F9 and apply Format > Number > Number.
This formula accurately calculates the total working hours excluding lunch break time, whether:
- The employee reports before lunch,
- The employee reports after lunch,
- The employee works a full day, and so on.
The formula can accommodate both fixed and variable lunch break durations for different employees.
If there is no lunch break for a specific employee, enter 00:00:00 under both the lunch break start and lunch break end columns.
In the case of night shifts, ensure you use timestamps in columns B, C, D, and E.
Formula Breakdown
This formula utilizes the MAP function to iterate through each element in multiple arrays and return a corresponding array containing the calculated results.
We’ll be working with four arrays: B2:B9 (start time), C2:C9 (lunch break start), D2:D9 (lunch break end), and E2:E9 (end time).
First, we’ll craft the formula for a single row, using specific cells like B2, C2, D2, and E2. Then, we’ll transform this formula into a custom LAMBDA function to apply it to all rows simultaneously using MAP.
Calculating Lunch Break Dynamically
Our formula calculates the working hours between a start time and end time and then subtracts the lunch break time, which is determined by a dynamic lunch break time formula.
Step 1: Dynamic Lunch Break Start Time
=MIN(MAX(B2, C2), D2)
This can be read as =MIN(MAX(start_time, lunch_break_start), lunch_break_end)
.
The MAX part returns the later of the start time or lunch break start time.
The result obtained should not exceed the lunch break end time. The outer MIN ensures this.
Step 2: Dynamic Lunch Break End Time
=MAX(C2, MIN(D2, E2))
This can be read as =MAX(lunch_break_start, MIN(lunch_break_end, end_time))
.
The MIN part returns the earlier of the end time or lunch break end time. This value should not be earlier than the lunch break start time. The outer MAX ensures this.
Dynamic Lunch Break Time
The dynamic lunch break time is calculated as:
=MAX(C2, MIN(D2, E2)) - MIN(MAX(B2, C2), D2) // step_2 - step_1
This formula is the key part of deducting the lunch break from working hours in Google Sheets.
Calculating Total Hours Excluding Lunch Break Time
The following formula will return the working hours, i.e., the difference between a start time and end time:
=(E2-B2)
To deduct the lunch break time from this, use the following formula:
=((E2-B2) - (MAX(C2, MIN(D2, E2)) - MIN(MAX(B2, C2), D2)))*24
Why Multiply by 24?
Google Sheets stores dates and times as serial numbers representing days since December 30, 1899, with a decimal portion for the time of day.
Subtracting the start time from the end time, as well as the lunch break start time from the lunch break end time, results in the difference in those days. Multiplying by 24 converts this difference to actual hours.
You can use this non-array formula in cell F2 and drag it down to deduct lunch breaks in Google Sheets. However, we have converted this to an array formula. Please find those steps below.
Custom Lambda Function and Final Formula
We can convert the above formula to a lambda function so that it can automatically spill the result without dragging it down.
Here is that custom function:
LAMBDA(s, lbs, lbe, e,
((e-s)-(MAX(lbs, MIN(lbe, e))-MIN(MAX(s, lbs), lbe)))*24
)
It follows the syntax LAMBDA([name, …], formula_expression)
.
We have assigned the names ‘s’, ‘lbs’, ‘lbe’, and ‘e’, to B2, C2, D2, and E2 and used them within the formula expression. The formula expression is derived from our non-array formula.
This is then used within the MAP function to return an array result.
Syntax: MAP(array1, [array2, …], lambda)
The arrays in MAP are B2:B9, C2:C9, D2:D9, and E2:E9. We have already coded the lambda function.
Resources
Here are some Google Sheets resources specifically for start-time and end-time calculations.
- Payroll Hours Calculation in Google Sheets
- Google Sheets: The Best Overtime Calculation Formula
- Combine Time In and Time Out into the Same Row in Google Sheets
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!