Calculating my work hours minus lunch break and then adding forweekly total
I'm having trouble creating a time worksheet. I want to subtract my start time from my end time, determine if I have worked longer than 4 hours (company automatically takes 30 mins for a lunch break off total if you work more than 4 hours), and then calculate total house worked. I'm having a problem with the lunch break portion of the calculation. I need a formula that will work. What can I do??

Calculating my work hours minus lunch break and then adding for weekly total

Calculating my work hours minus lunch break and then adding forweekly total
bobbethel schrieb am 26.03.2015 04:40:36 mit Betreff "Calculating my
work hours minus lunch break and then adding for weekly total": I'm having trouble creating a time worksheet. I want to subtract my start time from my end time, determine if I have worked longer than 4 hours (company automatically takes 30 mins for a lunch break off total if you work more than 4 hours), and then calculate total house worked. I'm having a problem with the lunch break portion of the calculation. I need a formula that will work. What can I do?? If I got you right, your company wants you to take a break of at least 30 minutes within the first 4,5 hours. If you work 4:10 hours before lunch, then you loose 10 minutes, not half an hour, right? I have similar conditions for breaks at work and I use the following since years. Say, you note your working time in "FromTo"pairs in cells A2 till F2. You can note up to three workingperiods. In G2 note the following formula: =IF(MAX(0,F2E2)+MAX(0,D2C2)+MAX(0,B2A2)<=4/24,MAX(0,F2E2)+MAX(0,D2C2)+MAX(0,B2A2),MAX(4/24,MAX(0,F2E2)+MAX(0,D2C2)+MAX(0,B2A2)+MIN(0,MAX(0,MIN(A2+4,5/24,MAX(B2,C2))B2)+MAX(0,MIN(A2+4,5/24,MAX(D2,E2))D2)0,5/24))) It gives, for example, the following results: From_1 To_1 From_2 To_2 From_3 To_3 Working hours*) 08:00 10:00 10:00 11:00 03:00 08:00 12:05 12:30 14:00 05:30 (5 min lost) 08:00 15:00 06:30 (30 min lost) 08:00 09:00 10:00 18:00 09:00 (lunch break:910) 08:00 09:00 09:15 14:00 14:30 17:00 08:00 (15 min lost) 08:00 09:00 09:15 09:20 09:25 13:00 04:30 *) Working hours with break of 0:30+ after at most 4 hrs The formula can be simplified, if you never use From_3, To_3 ([E3], [F3]). Regards, Alfred 
All times are GMT +1. The time now is 11:14 PM. 
Powered by vBulletin® Copyright ©2000  2021, Jelsoft Enterprises Ltd.
ExcelBanter.com