G
Guest
I will try to explain clearly what I am trying to accomplish. I hope this
makes sense.
I work in the transportation Industry and have the dubious pleasure of
designing a spreadsheet, which may be outside of my skill level.
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 have driver’s names and their assigned work hours in a separate table and
I can bring the regular hours into the worksheet using a lookup function.
What I need is a way to track and warn me if an operator exceeds the
allowable hours in any 7, 8, or 14 day period. The work periods are not
static. To put it another way, the drivers are always working the 7th, 8th or
14th day.
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.
Below is a formula I received from a gifted Excel professional but it does
not quite work. It is my fault for not making my problem clear enough. This
formula has made me re-think my strategy and I am now asking for assistance
based on this new format. I think this formula could work with a few
modifications.
I have decided to take the sound advise of using one spreadsheet instead of
several with a few additions.
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
??, 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.
Thank you in advance,
Martin
Perhaps you could store all the data on the same sheet. Then, keep a
running total per limit per driver.
For example, with dates in column A, names in column B, and hours in
column C, in cells D1:F1, enter the hour limits (60,70,120).
In cells D3:F3, enter the consecutive days (7,8,14)
In cell D4 enter:
=SUMPRODUCT(--($A$4:$A4>=$A4-D$3-1),--($B$4:$B4=$B4),--($C$4:$C4))
Copy this formula across to column F, and down to the last row of data.
Use conditional formatting to highlight the cells that are over the
limit in row 1.
A pivot table could summarize the data by week.
There's a sample file here:
http://www.contextures.com/excelfiles.html
Under 'Conditional Formatting', look for 'Highlight Amounts Over Limit'
makes sense.
I work in the transportation Industry and have the dubious pleasure of
designing a spreadsheet, which may be outside of my skill level.
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 have driver’s names and their assigned work hours in a separate table and
I can bring the regular hours into the worksheet using a lookup function.
What I need is a way to track and warn me if an operator exceeds the
allowable hours in any 7, 8, or 14 day period. The work periods are not
static. To put it another way, the drivers are always working the 7th, 8th or
14th day.
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.
Below is a formula I received from a gifted Excel professional but it does
not quite work. It is my fault for not making my problem clear enough. This
formula has made me re-think my strategy and I am now asking for assistance
based on this new format. I think this formula could work with a few
modifications.
I have decided to take the sound advise of using one spreadsheet instead of
several with a few additions.
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
??, E3:E?? respectively would be used to hold the formulabelow. I have tried to modify it but it will not work for me. Any help would
be appreciated.
Thank you in advance,
Martin
Perhaps you could store all the data on the same sheet. Then, keep a
running total per limit per driver.
For example, with dates in column A, names in column B, and hours in
column C, in cells D1:F1, enter the hour limits (60,70,120).
In cells D3:F3, enter the consecutive days (7,8,14)
In cell D4 enter:
=SUMPRODUCT(--($A$4:$A4>=$A4-D$3-1),--($B$4:$B4=$B4),--($C$4:$C4))
Copy this formula across to column F, and down to the last row of data.
Use conditional formatting to highlight the cells that are over the
limit in row 1.
A pivot table could summarize the data by week.
There's a sample file here:
http://www.contextures.com/excelfiles.html
Under 'Conditional Formatting', look for 'Highlight Amounts Over Limit'