Hi Bernie,

Thanks for the formula.

I am running into the same problem with this one. Perhaps a modification?

When I enter hours in B3 to B8 that total more than 60 then the true value
works. However if the driver has no hours for B9 as the restriction suggests
then when I enter hours for B10 I still get a true value as the formula is
totaling hours from B3 and it should total hours from B4 in order for the
calculation to be accurate. That is the driver is allowed to work a maximum
of 60 hours in 7 days. If he max's his hours in 5 days ( B3:B7)then he can't
work the next 2 (B8:B9). When he returns to work on the next day (B10), his
hours should be calculated from (B4:B10) and so on. Rows D & E will have
similar calculations based on their restrictions.

Or - Am I doing something wrong?

This is along the line of what I wanted.

Thanks
Martin

"Bernie Deitrick" wrote:

Martin,

With dates in column A, starting in row 3, and hours in column B, starting
in row 3.

Enter 60, 70, 120 in C1, D1, and E1, and enter 7, 8, 14 in C2, D2, and E2.

Then enter this formula in C3:

=SUM(OFFSET($B3,MAX(-(ROW($B3)-1),-(C$2-1)),0,MIN(ROW($B3),C$2),1))C$1

Copy to D3:E3, then copy C3:E3 down as far as you need.

This formula will return TRUE if they are scheduled to be on duty for beyond
the restrictions.

You could use similar formulas for conditional formatting: three
conditions - just enough.

Also, you could conditionally format columns A and B to turn red when any of
these values is TRUE.

HTH,
Bernie
MS Excel MVP

"Trying to excel in life but need help"
oft.com wrote in message
...
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: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.

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 he

http://www.contextures.com/excelfiles.html

Under 'Conditional Formatting', look for 'Highlight Amounts Over Limit'