Help With Drivers Hours Calculations

[Motzil]

Hi Everyone

I am a small transport company and I am trying to perfect a spread sheet that will record each drivers shift hours, Daily Rest and Weekly rest. I have created a framework that works in part. However it falls downs in certain areas. I have enclosed a copy so that you can see what I知 trying to achieve.

You will see that from rows H & I. I have a drivers Start Time and Finish Time. What I want to do in row K is workout the drivers daily rest. By taking the Finish time on Tuesday and the start time on the Wednesday and deduct those hours. As currently I知 having to enter it manually. Which is way too slow. However I am currently using Sum to deduct H from I to give me the drivers total shift hours.


I would then like to take row J and work out the total number of hours worked over a 6 day period. But sum will not work for this calculation. I not sure if this is due to time cell format or the fact that I知 using H&I already

My third task is to calculate a drivers weekly rest. i.e take his/her finish time on Fri/Sat and calculate the number of hours from their Start time on Monday or whatever day they return to work i.e 17:00 Friday to 04:00 Monday which is 59 hours and for this to be displayed in a stand alone cell.

I have been using excel for a few years. But only for basics and I don稚 wish to sound ungrateful when I ask if its possible for some to explain/show me how I need to make this calculation so that i can understand the principal should i have a similar problem later. I知 happy to play about till i get it right

Incidentally I am using office 365 on an iMac and on an iPad if this has any bearing.

[oeldere]

To get you started.

See sheet Oeldere.

After that a pivot table.

I did not get the correct value for rest time (yet).

[ORoos]

Hi Motzil,
Can you please add a bit more 'flesh to the bone'. In your sheet, are this the hours for Tuesday? Where do you record Wednesday. It would be helpful to know how the data is in your system in order to fins a solution.
Your data shows some trips (start end time/mileage).
How do you identify a specific driver. I assume the driving and rest hours are per driver. Where is the date for the trip.

EDIT: Sorry, saw date/day...

[ORoos]

in K2 try: =IF(I2="","",IF(H3="","",((H3+24)-I2))) and pull down.

[oeldere]

hereby the update file, with the correct rest time.

[ORoos]

For the daily hours worked; try J2 =(I3*24)-(H3*24) and pull down. Make sure you format cell as number.

[ORoos]

See attached and let us know if that is working correctly for what you need.