On Mon, 7 Mar 2005 11:36:49 +0800, "Max" wrote:
... the thing is, IF anywhere on Column A, there is a number
larger than 33, column C and E restart at Zero
plus whatever is in column B for that day.
...
Column D: Total from column C: minus 70
FWIW, going by the above lines and from studying the sample table given in
the original post for cols A to E, what's described below seems to return
the desired results in the computed cols C to E for the data input in cols A
and B ?? (But admit <g I couldn't correlate the part on the SUMIF example
mentioned for C7, E8, and the part about cols C/E meant to total the hours
on duty for the past 7/8 days. These parts were placed aside in coming up
with the set-up below .. )
Assume the data below is in A1:B11
(taken from the original post)
24 10
10 10
11 9
10 8
10 9
11 8
12 8
10 8
34 10
15 11
12 13
If you put
In C1: =B1
In C2: =IF(A2<=33,B2+C1,B2)
and copy C2 down to C11
In D1: =70-C1
and copy D1 down to D11
In E1: =B1
In E2: =IF(A2<=33,B2+E1,B2)
and copy E2 down to E11
(Col E is basically identical to col C)
The above will return in cols A to E,
the full pictu
24 10 10 60 10
10 10 20 50 20
11 9 29 41 29
10 8 37 33 37
10 9 46 24 46
11 8 54 16 54
12 8 62 8 62
10 8 70 0 70
34 10 10 60 10
15 11 21 49 21
12 13 34 36 34
which seems to correspond quite well with the results indicated in the
original post (Believe there's a typo in the value for C8 in the original
post: C8's computed value should be 70, not 60)
Hi Max,
Actually, No, it is supposed to be 70, If at any time I take over 33
hours off duty, My time automatically starts back at 70, minus of
course any hours that I work that day.
If I have not exceeded the 70-hour limit and take 34 consecutive hours
off duty, I have 70 hours available again. I would then begin my
totaling on the day of the restart and not go back the full 7 or 8
days.
Column C is keeping a running total for only 7 days
Column E is doing the same but for 8 days
Unless of course I restart my 70 by taking 34 consecutive hours off
duty.
I would like to elaborate more about what I am trying to accomplish
with these formulas..
Thanks .. Robert
pacrat2001 <at msn <dot com