Nested if, sum & vlookup Function

[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 iindustry 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 drivers names and their assigned work hours in several worksheets. I
can calculate hours worked for each day. The worksheets are labeled Week 1,
Week 2, Week 3 and so on. 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
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 in this 7 day stretch so he is forced
to take Sunday off. He returns to work on the next 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.

I have been trying to use this formula in the Monday cell of the Week 2
worksheet and continue changing it to calculate the previos seven days as I
move along to the next day.

Week 2 Monday Cell
=IF(SUM('Week 1'!E5:$K5)>=[60]:00,"Over 60hrs",VLOOKUP($C5,'Platform, Report
& Travel'!$A$6:$H$52,2,FALSE))

Week 2 Tuesday Cell
=IF(SUM('Week 1'!F5:$K5)>=[60]:00,"Over 60hrs",VLOOKUP($C5,'Platform, Report
& Travel'!$A$6:$H$52,3,FALSE))

Can anyone think of an easier or more sensible way?

I can either have all of the weeks in one file using worksheets or have each
week in it's own file.

Thank you in advance,
Martin

 

[Bob Phillips]

Martin,

Could I suggest a different design?

How about 1 sheet per driver.
List the drivers name in A1 and the sheet tab (these can be linked)
List the dates in A2:A378 (start 13 days before start of year)
In B2:B378 add the hours worked
In A15 is 01-jan, we add a conditional format of =SUM(B10:B18)>=70 and a
colour of say red
Copy the CF down
Similar formula for 8 and 14 day tests

This way all transgressions get highlighted in colour.

If this seems a good approach to you, I could knock you up a sample
workbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Trying to excel in life but need help"

 

[David Jessop]

Hi,

I think that I would try to break down the calculation into various bits,
rather than doing it in one hit. Mainly to make it easy to see what is
happening.

My approach (and I'm sure there are others) would be to have one master
sheet with drivers names across the top and dates down the side, and each
cell in the sheet would be the hours worked that day.

You can then simply create another three sheets giving the running total
over 7, 8 and 14 days. Call these Sum7, Sum8 and Sum14

Then in the Week1, Week2 sheets (if you need them) you can simply reference
the relevant cells (using =INDEX and a single cell which contains the row of
the start of the week). So in Week2, the start of the week would be probably
row 8 (in A1 let's say), so

=INDEX(Sum7!$A$1:$C$366,$A$1,2)

would give the 8th row, 2nd column. The advantage of this is you can set up
week1 and just copy it to Week2, Week3 ... and just change one number. If
the workbook gets too large (or slow) you can always Copy, Paste Special,
Values on the old sheets.

I hope this makes sense.

regards,

David Jessop

I will try to explain clearly what I am trying to accomplish. I hope this
makes sense.

I work in the transportation iindustry 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 drivers names and their assigned work hours in several worksheets. I
can calculate hours worked for each day. The worksheets are labeled Week 1,
Week 2, Week 3 and so on. 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
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 in this 7 day stretch so he is forced
to take Sunday off. He returns to work on the next 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.

I have been trying to use this formula in the Monday cell of the Week 2
worksheet and continue changing it to calculate the previos seven days as I
move along to the next day.

Week 2 Monday Cell
=IF(SUM('Week 1'!E5:$K5)>=[60]:00,"Over 60hrs",VLOOKUP($C5,'Platform, Report
& Travel'!$A$6:$H$52,2,FALSE))

Week 2 Tuesday Cell
=IF(SUM('Week 1'!F5:$K5)>=[60]:00,"Over 60hrs",VLOOKUP($C5,'Platform, Report
& Travel'!$A$6:$H$52,3,FALSE))

Can anyone think of an easier or more sensible way?

I can either have all of the weeks in one file using worksheets or have each
week in it's own file.

Thank you in advance,
Martin

 

[Debra Dalgleish]

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'

I will try to explain clearly what I am trying to accomplish. I hope this
makes sense.

I work in the transportation iindustry 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 drivers names and their assigned work hours in several worksheets. I
can calculate hours worked for each day. The worksheets are labeled Week 1,
Week 2, Week 3 and so on. 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
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 in this 7 day stretch so he is forced
to take Sunday off. He returns to work on the next 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.

I have been trying to use this formula in the Monday cell of the Week 2
worksheet and continue changing it to calculate the previos seven days as I
move along to the next day.

Week 2 Monday Cell
=IF(SUM('Week 1'!E5:$K5)>=[60]:00,"Over 60hrs",VLOOKUP($C5,'Platform, Report
& Travel'!$A$6:$H$52,2,FALSE))

Week 2 Tuesday Cell
=IF(SUM('Week 1'!F5:$K5)>=[60]:00,"Over 60hrs",VLOOKUP($C5,'Platform, Report
& Travel'!$A$6:$H$52,3,FALSE))

Can anyone think of an easier or more sensible way?

I can either have all of the weeks in one file using worksheets or have each
week in it's own file.

Thank you in advance,
Martin

 

[Trying to excel in life but need help]

Thanks for the help.

While these are great suggestions I can't use any of these solutions.

I have over 300 drivers that work on a rotating 3 week schedule. Regular
hours are calculated automatically using a vlookup function based on the
date. A driver, for example would work group 1A on week1, 1B on week2, and 1C
on week3 and week4 would be 1A again and continue on this cylce. The lookup
table looks for the date reference under each drivers name and returns the
corrosponding group. The regular work hours are looked up by the group value
(e.g. 1B).

I do thank you for your time and help.

Martin