help

[tweekedteresa]

I have written a spreadsheet to calculate available (next
day) time left to work for a truckdriver. However I don't
know the formula/function I can use to complete one of
the columns - here it goes .....
in columns c4-c10 are the hours from the last 7 days of
the previous month... then the new months starts with
c13 - consecutively down the c column.

Beginning with d13 consecutively down the d column I
need "HRS WORKED SINCE LAST 34 CONSECUTIVE HRS OFF" - so
basically I need a formula that will look at the hours
worked column (specifically where hrs worked are). Then
decide when/if the last two cells containing hrs worked -
are zeros. And and add all days after the last two cells
in a row that contain zero!
Please help

 

[Guest]

I'm not sure if I follow what you're trying to do, but here's a stab at it.

=IF(C4+C5=0,SUM(C6:C10),IF(C5+C6=0,SUM(C7:C10),IF(C6+C7=0,SUM(C8:C10),IF(C7+
C8=0,C9+C10,IF(C8+C9=0,C10,SUM(C4:C10))))))

If there aren't 2 consecutive days of 0 hrs, then the hrs worked for all 7
days are summed.


In case there are negative hours:

=IF(AND(C4=0,C5=0),SUM(C6:C10),IF(AND(C5=0,C6=0),SUM(C7:C10),IF(AND(C6=0,C7=
0),SUM(C8:C10),IF(AND(C7=0,C8=0),C9+C10,IF(AND(C8=0,C9=0),C10,SUM(C4:C10))))
))

Good Luck,
Robert