Need Formula

[K. Grass]

Hey guys,

I am a professional driver and my workbook is setup to keep track of my
hours on the road on a day-to-day basis. With the new change in the DOT law
I need a solution that complies with those variations but works with this
setup:

The days of the month run down column A contiguously and each days hours are
tracked across rows. For example; Cell E6 is the Drive Time. Cell F6 is the
On Duty hours. Cell G6 contains the formula; =SUM(E6:F6). Now, cell I6 adds
that total to include the previous 5 cells in column G to come up with a
total (the last six days) on duty.

Okay, cell K6 is the constant of 60:00 hours which contains the formula;
="60:00"-I6 to come up with the available hours for the next day for each
day of the month. Column K is the Available Tomorrow hours column and the
results run down accordingly.

So what I need exactly is for column K (each cell) to calculate the
available hours tomorrow with the caveat that it will reset to 60:00 if the
value of the preceding cells in the G column are 0:00 in the past 34:00
hours. Is this possible? Please help, I'm at my wits-end here.

P.S. The entire spreadsheet is time formatted - (00:00).

TIA, Ken G.

 

[Frank Kabel]

Hi Ken

I think you already posted this topic (sound familiar to me . It
would be better to stay in the thread.

To your problem: I think I understand your issue besides the 34:00
hours topic. You only have drive time, on duty time, etc. And 34 hours
cannot be divided into full days. As you have only full days in column
G (if I understood you correctly) how should 34 hours be calculated.

Maybe you can give some more information. You can - if you like - email
me your spreadsheet and I'll have a look at it.
Frank