Trying to excel in life but need help wrote...
....
The Highway Traffic Act in Ontario, Canada stipulates that a truck or
bus
driver is restricted to the following hours of work:
A driver may not drive a truck or a bus after being on duty for,
a) 60 hours in 7 consecutive days, or
b) 70 hours in 8 consecutive days, or
c) 120 hours in 14 consecutive days.
....
I may just be too dense, but I don't see how (a) and (c) aren't
redundant. In order to work 120 hours in a 14 day period, it'd be
necessary to work 60 hours in either or both the first or last 7 days
of the 14 days. If drivers are prevented from driving 60 hours in any
7 day period, it's mathematically impossible for them to drive 120
hours in any 14 day period. Unless there are further, undstated rules
about adjusting for days off after 7 or 8 day periods.
This also seems to allow drivers to drive 8.5 hours per day
indefinitely.
As an example;
A driver starts work on Monday, before he reaches Sunday he has
accumulated
60 hours and must not continue to work during the remainder of this 7
day
stretch so he is forced to take Sunday off. He returns to work on
Monday. His
seven-day stretch does not start over. He must now calculate the hours
worked
from the previous Tuesday to determine how many hours he is allowed to
work.
....
So if a driver drove 54 hours between Monday and Saturday and was
scheduled to drive 8 hours on Sunday, would that driver be allowed to
drive 6 hours on Sunday or not allowed to drive at all until the
following Mondday?
I would like to create a template and use the file for each driver who
works
extra hours. In A1 I would type a drivers name, which would return
values via
a lookup table to represent a scheduled set of hours over a 12 week
period.
These hours would be imported via the lookup into B3:B??. Each workday
would
be listed by date from A3:A??. Then the formula below would be used
for the
calculation and the Conditional format command would warn me of any
violations. The regular scheduled hours for the driver would occupy
the dates
for the entire 12 weeks. Days off would be blank. I would insert
another
column or columns that the extra work could be enter in and column
B3:B??
would contain a nested formula to return this value.
Column C3:C??, D3:D??, E3:E?? respectively would be used to hold the
formula
below. I have tried to modify it but it will not work for me. Any help
would
be appreciated.
....
In cell D4 enter:
=SUMPRODUCT(--($A$4:$A4=$A4-D$3-1),--($B$4:$B4=$B4),--($C$4:$C4))
Sure looks like C3:E3 would need to hold 7, 8 and 14, respectively.
I'm going to assume there are no problems populating dates in col A
starting in cell A4 and hours in col B starting in cell B4. I'll assume
C2:E4 contains {60,70,120;7,8,14}. I'll enter "Status" in cell F3.
Now for formulas. On the first day (cell A4), it's a certainty the
driver will be driving the full schedules hours, so select C4:E4, type
the formula
=$B4
and press [Ctrl]+[Enter] (*DONT'T* hold down [Shift]). In F4 enter the
array formula
=IF(AND(C4:E4<=C$2:E$2),"Driving","Off")
Now the fun. In cell C5 enter the formula
=$B5+SUMIF(OFFSET($F5,-1,0,MAX(1-C$3,ROW($B$4)-ROW()),1),"Driving",
OFFSET($B5,-1,0,MAX(1-C$3,ROW($B$4)-ROW()),1))
Fill C5 right into D5:E5. Fill F4 down into F5. Then select C5:F5 and
double click on the Fill Handle (the square in the bottom-right corner
of the border around the selected cells) to fill C5:F5 down to match
the entries in columns A and B.
Note: this assumes that if a driver were to exceed any of the max hours
on a particular day, then that driver wouldn't drive at all that day
rather than drive just until they reach the max hours. In the previous
example I gave the driver would be off all day on Sunday.
|