log book

[Joe]

Ok here's one that's been killing me This version works fine

B10 to 40 are numbered 1thru 31

C10 thru 40 represent the days of the month
with C3 thru 9 representing the 7 previous days of last month.
Hours on duty are entered into C3 - 40 for each day worked

D9 - 40 are the accumulated hours for the past 7 days
D9's formula is =SUM(C3,C4,C5,C6,,C7,C8,C9) this is only
for D9
D 10 - 40 is =IF(AND(ISBLANK(C10)),"",(F10-C3))
the cell references progress down to D40

E9 is the number of hours available for the next day ( limited to 70 and
below)
E9=70-D9

E10 - 40 =IF(AND(ISBLANK(C10)),"",(70-D10))

As I said it works perfectly. The problem is they changed the rules on me!!
Now when you take a consecutive 34 hours off you can reset your available
hours back to 70.
What I need is a way to do this in the sheet. I think I need to divide the
cell values by 24 or use a count or count if, but alas I'm not on that level
yet. Can anyone help me out please.
I am also just starting with Macros so if one is involved please be explicit


Answer in group or e-mail
(e-mail address removed)

 

[Trevor]

Joe,

I've found a solution, but it's not pretty. I turned your spreadsheet
upside down, then I added two "helper" columns which will tell you when a
"reset" occurs (someone takes 34 hours off).

B3 to B33 numbered from 31 down to 1
C3 to C40 available to accept hours on duty for each day
D3 to D40 displays the number of hours worked in the last 7 days, OR since
the last "reset" (when 34 hours off occurred)
E3 to E40 the number of hors available to be on duty for the next day

Assuming people can be on duty for 24 hours in a day, then their clock
"resets" when they don't work for a whole day AND they didn't work more than
14 hours for the previous day. Therefore, if you make column G a reset
indicator:
G3=IF(AND(C3=0,C4<=14),"R","")
copied down to G40

Now, Add a second helper column, column H, which will tell you how many days
ago a reset occured, if it occurred within the last 7 days.
H3=MATCH("R",G3:G10,0)
copied down to H40

Next, create column D (hours so far). If column C is blank, enter ""
otherwise, calculate the answer. To calculate the answer, you will see if
there was a reset in the last 7 days. If there wasn't, you'll have a #N/A
value in column G. So if there was no reset, just sum the last 7 days,
otherwise, sum the last H days (actually, row H counted today as the first
day, so we subtract 1 from the value in H).

D3=IF(ISBLANK(C3),"",IF(ISERROR(H3),SUM(C3:C9),SUM(OFFSET(C3,0,0,H3-1))))
and copy down to D33. Change the formula slightly

D34=IF(ISBLANK(C34),"",IF(ISERROR(H34),SUM(C34:C$40),SUM(OFFSET(C34,0,0,H34-
1))))
and copy down to D40

Finally, create column E (hours available for tomorrow). This is simple,
now, because column D is a running total of the last 7 days or since the
last reset, so simply subtract that from 70.
E3=IF(ISBLANK(C3),"",70-D3)
and copy down to E40

Feel free to hide columns G and H, as they are ugly.

Trevor

PS - Note that your spreadsheet deals with whole days. This can cause a
problem if someone is on duty for 14 hours from 10:00am to midnight on
Monday, but does not work at all on Tuesday. As far as the spreadsheet is
concerned, they will have one empty day and the prior day will be 14 hours,
which does match the test that within those 2 days, there were 34 hours
during which they were not on duty. So the spreadsheet resets their clock.
When in reality, the person was not off duty for a 34-hour block. You'll
need to change to tracking by the hour, instead of the day, if you want to
be accurate.

 

[Joe]

Thanx Trevor,

Ill get back and let you know how it worked

BTW what was the reason for turning it upside down?

 

[Trevor]

Joe,

The MATCH command looks at a range of cells, and I don't know how to get
Excel to stop from automatically putting a range reference into the format
G3:G10, even though you type G10:G3. And I want it to find the most recent
reset. And for some reason, Excel didn't like the syntax of
MATCH("R",{G10;G9;G8;G7;G6;G5;G4;G3},0) which is what I really wanted to do.

So with the spraedsheet the way you had it, I could use the MATCH command
but change the third parameter to give me the last occurance of a reset (the
most recent one). However, the problem with that is that if no reset
occured within the last 7 days, that version of the MATCH command still
returns the number 7. Which incorrectly indicates that a reset occurd on
the 7th day.

So, rather than fixing the whole problem, I just found it easier to turn
within the last 7 days, but then MATCH will return 7 when no reset at all
occured in the

Trevor