Sumproduct help needed!

[Trying to excel in life but need help]

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'

 

[Bernie Deitrick]

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"

 

[Trying to excel in life but need help]

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]

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"

 

[Trying to excel in life but need help]

Hi Bernie,

May e-mail address is: (e-mail address removed)

Thanks,
Martin

 

[Harlan Grove]

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??, 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.