Trucker's logbook recap in Excel

[Kathy Tracy]

I'm a truckdriver. I need to build a simple working hours/mins recap. Does anyone have a clue how to do this? Working in hours/mins is blowing my mind. I can do the formula for the simple math to add up the total hours/mins but when I try to subtract that total from 70 hours, I can't get it to work. Any help appreciated and thanks in advance!

[#2]

Hi Kathy,

Think about the time in numbers. In Excel

One day = 1

One hour = 1/24

One minute = 1/24/60

The rest is mainly formatting to present such numbers in time format.

Thus if in cell A1 you have 10 hours as 10:00 and you want to subsruct from 70 hours that will be

=70/24-A1

and if you format resulting cell as [h]:mm the result will be 60:00

If you attach small sample we could be more concrete.

[#3]

Here are my last 8 days of hours: 

7/26 8:44, 7/27 7:26, 7/28 11:14, 7/29 8:12, 7/30 6:42, 7/31 10:30, 8/1 10:51, 8/2 0:00. What I need is a spreadsheet where I can enter the current day's hrs/mins and the spreadsheet will add all the hrs/mins up, then subtract the oldest day's hrs/mins then subtract that total from 70 hrs. It needs to roll the oldest day off.  If I can get that much done, then I'll try to figure out how to make it consider the 34 hour restart rule. Everything I know about Excel is self-taught and these are complex formulae that are way past my abilities. Thanks so much for helping!

[#4]

Here and attached is mock-up

In first column you manually put any symbol into the cell for the first date of the period. Next two columns are date and time. Next is cumulative time in the period like

=IF(ISBLANK(B5),N(E4)+D5,D5)

and the last column shows how much time do you have till end of the period.

If you have some rule how to shift from one period to another you most probably don't need first and last column using the formula for the rule in calculating cumulative time in the period.

[#5]

@Sergei Baklan 

I am a truck driver also and downloaded the example file for tracking hour of service.  It is fantastic!!!  Thank you!

[#6]

@Ptgizmo , you are welcome, glad to help

[#7]

Has there been any update for The 70 hour clock and to count the 8th day recap? @Sergei Baklan 

[#8]

@SgtJBigPapa 

Not sure I understood, could you please illustrate on the sample what is required?

[#9]

@Sergei Baklan 

In trucking, if you take off for 2 days, it resets your clock all back to 0 as shown before day 1.  What would be the best setup because you can't do auto sum after that part? 

[#10]

@Rodney9346 

Perhaps you may use

=IF( AND(B10=0, B11=0), 0,  MIN( N(C10)+B11,  SUM(  INDEX(B:B, MAX(1,ROW()-6) ):B11 ) ) )

Please check in attached file.