Deducting Lunch Break Time From Total Hours in Google Sheets

Published on

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.

Example: Automatically Deduct Lunch Breaks in Google Sheets

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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

8 COMMENTS

  1. 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! 🙂

  2. 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.

  3. 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.

  4. 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.