HomeGoogle DocsSpreadsheetHow to Deduct Lunch Break Time From Total Hours in Google Sheets

How to Deduct Lunch Break Time From Total Hours in Google Sheets

Published on

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.

Sample Data to Deduct Lunch Break Time

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.

  1. Employees punch out before or during the lunch break.
  2. Punch in during or after the lunch break.
  3. 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)
Advanced Formula to Calculate Hours Worked and Minus Lunch Break Time

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.

Example Sheet 170422

Related Articles:

1. Google Sheets: Split Night Shift Job Hours into Two Columns.

2. The Best Overtime Calculation Formula in Google Sheets.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

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