When deducting lunch break time from total working hours in Google Sheets, consider these points:

1. An employee might work a half-day, either leaving before lunch or arriving after lunch.
2. Lunch break times may vary by shift.
3. 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