Formulas used with Sum Function

[gearhead03]

Excel 2000.
I have a worksheet that I use to keep track of my truck driver's hours to make sure they don't go over.
They can have 70 hours on duty in 8 days.
I enter their daily "on duty" hours in column "D" and then in column "E" I sum them using Sum(d1:d8) and copy that down the page for the whole year.
The other rule is that if they take 34 hours "off-duty" consecutively the clock restarts.
I have a column labeled "restart". It is a YES/NO.
What i would like for the sheet to do is in column "E" if the restart is "yes" start summing from that point on.

How can I do this?

Mark A. Kale

 

[SkipVought]

Hi,

Please post a SAMPLE of data that you are working with and the results you expect to see. Please include all relative instances.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[gearhead03]

left to right
The 1st column is the date
The next column is "driving time"
The next is "on duty " time
The next is a total of "driving time" and "on duty" time
The next is a sum of the past eight days "driving" and "on duty" time.
The last column just has either blank or yes. Yes would denote a restart and the sum would start at zero form that point.


01/01/08 10 1.5 11.5
01/02/08 8.5 2 10.5
01/03/08 5.5 1.5 7
01/04/08 7 2 9
01/05/08 8 3 11
01/06/08 5.5 1 6.5 55.5
01/07/08 4.5 1 5.5 61
01/08/08 8 3 11 72
01/09/08 2.5 1.5 4 64.5
01/10/08 9 2 11 65
01/11/08 2.5 2 4.5 62.5
01/12/08 0 0 0 53.5 YES <--restart sum here
01/13/08 1.5 1 2.5 45 <---this would read 2.5
01/14/08 8.5 1.5 10 48.5 <---this would read 12.5
01/15/08 10.5 2.5 13 56 <---this would read 25.5
01/16/08 11 2 13 58 <---this would read 38.5


Mark A. Kale

 

[SkipVought]

What happens when they have MORE than 70 hrs in 8 days, as your example shows?


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[Gavona]

In this example I assume you have the formula for calculating the "Yes" and that this is in column G.

If you are summing the hours in column E then in cell E18 the formula would be:

=SUM(OFFSET(D18,-8+F18,0)18)

In cell F18 the formula would be:

=IF(G18="yes",8,MAX(F17-1,0))

Copy these formulae up and down.
Consider conditionally formatting column E so that hours in excess of 70 are highlighted.

I am sure Skip will come up with something more elegant and complete!

Gavin

 

[SkipVought]

"They can have 70 hours on duty in 8 days"

As gavin posted, the OFFSET function returns a range. I would approch it a bit different. Here's the formula in row 18...
[tt]
=SUM(OFFSET(D18,-8,0,9,1))
[/tt]
This sums NINE days. You cannot have NINE days of values if EIGHT of them equal 72.

"if they take 34 hours "off-duty" consecutively the clock restarts. "

I don't think that you have enougt data to determine this. Suppose these two examples.
[tt]
day1 - 2 driving/on duty hours during the FIRST two hours of the day

then the driver takes 34 hrs

the driver can resume on day 2 at noon
[/tt]
example 2
[tt]
day 1 - 2 driving/on duty hours during the LAST two hours of the day

then the driver takes 34 hrs

the driver can resume on day 3 at 8 AM
[/tt]
if I understand the rules.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[gearhead03]

I am using this spreadsheet to keep track of the hours they drive.
If it goes over 70 hours on the sheet that is ok. I have a conditional format that highlights that cell to let me know there is a problem somewhere along the line that I need to go back and look at.
What I want to accomplish is if on the 6th day he has a restart it will reset the sum from that point.
For example at the end of day 5 (11:59pm) he has 70 hours on duty (14 hours each day)
On day six he restarts the clock with 34 hours off duty
on day seven he has 14 hours on duty
on day eight he has 14 hours on duty

With my current system it would look like this:
(COLUMN LETTERS CHANGED FROM PREVIOUS)
COLUMN "D" IS C+B
COLUMN "E" IS SUM(D18) and continues down the page day9 column "E" would be SUM(D29) day10 SUM(D310) etc...
I have one year set up on the worksheet.

A B C D E
Day1 10 4 14 14
Day2 10 4 14 28
Day3 10 4 14 42
Day4 10 4 14 56
Day5 10 4 14 70
Day6 00 0 00 70 YES
Day7 10 4 14 84
Day8 10 4 14 98
The cells on days 7 and 8 get highlighted but they are actually in violation.

I would like it to look like this:

Day1 10 4 14 14
Day2 10 4 14 28
Day3 10 4 14 42
Day4 10 4 14 56
Day5 10 4 14 70
Day6 00 0 00 00 YES
Day7 10 4 14 14
Day8 10 4 14 28

Skip, the restart I enter the "yes" manually. In order to get a 34 hour restart the driver has to take at least one full day off. The "yes" is entered on that day. Mainly to keep things simple as possible.

I hope this makes things clearer and thanks for your help.


If anyone is up for a real challenge I will post all the rules for hours a trucker can drive and we can hack out a formula!!!



Mark A. Kale

 

[SkipVought]

You are NOT calculating 34 hours then. You are assuming 34 hours, based on one day off, which is not necessarily true.

Did you look at the previously posted formula? It can EASILY be modified to meet YOUR criteria...
[tt]
=SUM(OFFSET(D18,-8,0,9,1))
[/tt]




Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[gearhead03]

I would not be assuming since I am looking at the drivers log book and can add the hours before or after the day off to see if he has a 34 hour restart.

Mark A. Kale

 

[Gavona]

Did either my solution or Skip's do the job for you?
Clearly you need to understand the offset function.....

Gavin

 

[SkipVought]

I guess that my point is that the data that you record in the workbook, is not the same PRECISION as the data you are reading in the logbook. THEREFORE, subsequent calculations may not accurately reflect reality.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[gearhead03]

I was working on it at the office friday and things got busy.
I am working another issue right now.
Hopefully later I will be able to address this again.
Updates as they develop.

Thanks

Mark A. Kale