Calcuating Elapsed Time

[syoung7903]

My brain hurts from trying to figure this one out. Please help!

I have for 100 employees clock in and clock out times for each day of
the month. I need to calculate a rolling 7 day of hours worked. But
my rolling number resets to 0 if an employee has not worked in the past
34 hours. So for example:

Emp1 1/1/2004 8:00 5:00 9 hours 9 hours
Emp1 1/2/2004 8:00 5:00 9 hours 18 hours
Emp1 1/3/2004 8:00 5:00 9 hours 27 hours
Emp1 1/4/2004 Off
Emp1 1/5/2004 8:00 5:00 9 hours 9 hours

etc

Its for tracking employees who go over 70 hours in any 7 day period.
But if they are off for 34 hours their rolling time resets.

Can anyone help.

Thanks!

 

[Squid]

I think you gave me a headache! But try this:
A B C D
1 8:00 AM 5:00 PM 9.00 9.00
2 8:00 AM 5:00 PM 9.00 18.00
3 8:00 AM 5:00 PM 9.00 27.00
4 - -
5 8:00 AM 5:00 PM 9.00 9.00
6 8:00 AM 5:00 PM 9.00 18.00


This is formulas for the above columns:
C D
=(B1-A1)*24 =C1
=(B2-A2)*24 =IF(C2=0,IF(D1<=34,0,C2+D1),C2+D1)
=(B3-A3)*24 =IF(C3=0,IF(D2<=34,0,C3+D2),C3+D2)
=(B4-A4)*24 =IF(C4=0,IF(D3<=34,0,C4+D3),C4+D3)
=(B5-A5)*24 =IF(C5=0,IF(D4<=34,0,C5+D4),C5+D4)
=(B6-A6)*24 =IF(C6=0,IF(D5<=34,0,C6+D5),C6+D5)