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

"Trying to excel in life but need help" wrote:

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