Forum Discussion
Trucker's logbook recap in Excel
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!
9 Replies
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.
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!
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.
