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

Hi,

Am Thu, 26 Mar 2015 04:40:36 -0700 (PDT) schrieb :

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

start time in A1, end time in B1.
Then try:
=IF(B1-A11/6,B1-A1-1/48,B1-A1)
or
=B1-A1-(B1-A11/6)*1/48


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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 "From-To"-pairs in cells A2 till F2.
You can note up to three working-periods.
In G2 note the following formula:
=IF(MAX(0,F2-E2)+MAX(0,D2-C2)+MAX(0,B2-A2)<=4/24,MAX(0,F2-E2)+MAX(0,D2-C2)+MAX(0,B2-A2),MAX(4/24,MAX(0,F2-E2)+MAX(0,D2-C2)+MAX(0,B2-A2)+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:9-10)
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