Home 
Search 
Today's Posts 
#1




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??

#2




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

#3




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
I need to calculate hours worked from 11pm to 7am minus a lunch  Excel Worksheet Functions  
Calculate hours worked minus a half hour for lunch  Excel Worksheet Functions  
how to calculate hours worked daily & minus 30 min lunch, 7am5pm  Excel Discussion (Misc queries)  
How can I calculate daily hours worked minus lunch to eual a decim  Excel Worksheet Functions  
Calculating total work week hours  Excel Worksheet Functions 