|
|
|
|
Martin,
I think you are doing something wrong. I will send you an example workbook
that works as you described. Contact me or post your email address.
HTH,
Bernie
MS Excel MVP
"Trying to excel in life but need help"
oft.com wrote in message
...
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'
|