Tracking Driver Hours

[Sean Dunn]

I am trying to develop a spreadsheet which tracks the hours of service for
our drivers; I have the easy part figured out however I am unable to create a
formula that resets the hours worked back to zero once the driver has taken
at least 34-hours off. Heres an example:

Day 1: 14 hours
Day 2: 10 hours
Day 3: 12 hours
Day 4: 14hours
Day 5: 14 hours
Day 6: 0 hours
Day 7: 0 hours
Day 8: 14 hours

The formula must calculate the hours over the last 8 days at any point in
the year (anything over 70 is a violation unless they take at least 34-hours
off); once they take 34-hours off the clock restarts and they have another
70-hours available. In my example the first 7-days equals 64 hours but on the
eighth day the calculation will equal 78: a clear violation. I need a formula
(preferably a button which can be clicked) which would enable me to reset
their total hours on Day 7 back to zero and restart calculating the 70 hours
over 8 days again. I envision that each line of the spreadsheet would have
this button, or restart option, as drivers take their days off on different
days. The spreadsheet would be 365 days long and each driver would have their
own sheet, unless someone can show me how to get 100+ drivers and all this
information for each on one spreadsheet.

Assistance with this challenge will be greatly appreciated.

 

[Kassie]

You can go 2 routes here.
One is to record work hours, and rest hours in between shifts.
The other is to record actual starting and ending dates and hours, and then
adding worked hours between prolonged rest periods. Iow, where a person has
been off for at least 34 hours, start adding working hours from 0 again,
regardless of the number of hours worked since the previous rest period.
Maybe mail me, and I am sure we can work something out.

--
HTH

Kassie

Replace xxx with hotmail

 

[Kassie]

Having had a closer look at your problem, I would do the excercise a bit
different, although the outcome would be the same.

I envision that each line of the spreadsheet would have this button: No buttons will be required, as formulae will handle the situation.
The spreadsheet would be 365 days long: I would not use a line a day, but rather a line a working shift.
each driver would have their own sheet: Definitely not, since you would need only 6 columns per driver!That can give you 36 drivers across, and even with 400 lines each, a total of 1 200 lines, leaving you with an additional >64000 unused lines in Excel 2003.

--
HTH

Kassie

Replace xxx with hotmail