In this juicy tutorial, let me show you how to effectively deduct lunch break time from total hours in Google Sheets. Whether you’re managing payroll, creating salary slips, or calculating equipment rental, time calculation is essential. With just a little exposure to Time functions, you can easily perform such calculations in Google Sheets. So let’s dive in and learn how to subtract lunch break time from total hours!
Sample Data
To start off, let’s take a look at some sample data that we’ll be using for our basic formula example. In this tutorial, we’ll mainly be using two Time functions – HOUR and MINUTE. If you’re not familiar with how to use these functions in Google Sheets, don’t worry! I’ve got you covered with my Functions Guide.
Basic Formula to Subtract Lunch Break Time From Total Hours
Now, let’s get into the nitty-gritty of the formula. Here is the master formula I’ve used in the above example to calculate working hours after deducting lunchtime:
=ArrayFormula((HOUR(C2:C11-B2:B11+E2:E11-D2:D11))+(MINUTE(C2:C11-B2:B11+E2:E11-D2:D11)/60))This formula works when employees punch in before the lunch break begins and punch out after the lunch break ends. It’s a dynamic formula that works regardless of the in and out times. Pretty neat, right?
Formula Explanation
The formula to find the total working hours excluding lunch hour is a combination of two parts:
Formula 1
HOUR(C2:C11-B2:B11+E2:E11-D2:D11)Using the HOUR function, we can return the hours from the time. It calculates the total hours from duty start time to lunch break, and adds that value to the total hours from lunch break end time to duty end time. This gives us the total hours worked.
Formula 2
MINUTE(C2:C11-B2:B11+E2:E11-D2:D11)/60With the MINUTE function, we can get the minutes. However, the returned value is in minutes, not hours. So I’ve converted this value to hours by dividing it by 60.
The final formula is a combination of Formula 1 and Formula 2, and with the help of ArrayFormula, it gives us an expanded result.
Advanced Formula to Calculate Hours Worked and Deduct Lunch Break Time
The basic formula may not work in certain cases where employees punch out before or during the lunch break, or punch in during or after the lunch break. But don’t worry, I’ve got an advanced formula for you!
Using a few IF logical tests, we can generate work start and end times for the calculation after dynamically adjusting the lunch breaks. Here’s the generic formula:
=ArrayFormula((hour(dynamic_work_end_time)-hour(dynamic_work_start_time))+(minute(dynamic_work_end_time)-minute(dynamic_work_start_time))/60)To calculate dynamic_work_start_time and dynamic_work_end_time, use the following formulas:
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))))Finally, combine all the elements to get the advanced formula to calculate hours worked and deduct lunch break time:
=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)And that’s it! With this advanced formula, you can dynamically deduct lunch break time from total hours in Google Sheets.
If you have any questions or need further clarification, feel free to drop a comment below.
I hope you found this tutorial helpful. Stay tuned for more tips and tricks on Crawlan.com!
