Home » Spreadsheets » Trucking Spreadsheet

Trucking Spreadsheet

Post by Jenny | March 19, 2019 | Spreadsheets | 10605 Views | No Comments

Trucking Spreadsheet – After my journey through unemployment I have had the fortune of joining an international transport company. My problem is in accurately calculating the date, including the time at which a truck must depart to arrive within the period indicated by the client.

Taking into account that in the transport there are rules of driving times, breaks and stops I decided to create an excel to perform this task. But I can not get it for more than I try and for that I ask your kind help.

Applicant Tracking Spreadsheet Excel And Trucking Accounting Spreadsheet

I summarize:

– Average Speed Truck: 80 Km / h

– After 4.5 hours of driving you must stop and rest 45 minutes, after the rest you will drive 4.5 hours more.

– Maximum driving hours in a day 9 hours

– After 9 hours of driving, the driver must rest 9 hours in a row

Excel example in the attachment

Destination: Dijon, France

Distance: 1,172 Km

Delivery date: 02/19/2013

Delivery time: 08:00 hours

thank you so much for your attention.

Details

I have reviewed the template you have made and I congratulate you. I have only found a small error in the calculation of rest periods of 9 hours due to rounding = ROUND.MEN (B10 / 2; 0). Changing it to = ROUND.MAS (B10 / 2; 0) if that seems to predict them well.

One of the longest trips we do is about 2150 km, in this case only calculated a stop when there are 2 periods of 9 hours. The 45min periods come out perfectly.

It seems a simple calculation but we have 25 trucks in constant movement, once downloaded abroad we look for loads (cabotage) for the return and it is very difficult to confirm to a client that the agreed deadlines can be met without performing these calculations previously.

I attached the file again and said, MANY thanks for your help

PD: If you have no objection to the change in the rounding formula would solve this issue.

Trucking Spreadsheet

Answer

with the permission of friend @ [uSER = 40962] SEBASTIAN67 [/ uSER], I leave an example that I do, I hope it serves you

The idea is this:

1.- fill in the yellow fields in column B,

2.- Initially a calculation of the estimated date of departure is made (cell B16), this date considers the hours of margin

3.- copy said date in cell G1 (it must be copied manually (paste values only), if the formula is copied it gives a cross-reference error).

4.- if the delivery date / time is within a driving period (the respective box in column I is green), everything is OK, the calculated date / time is correct.

5.- If point 4 is NOT met, the estimated date of cell B17 must be copied and pasted into cell G1 (values only), now the date / time of delivery will be within a driving period.

perform it in this way since it is logical that the delivery is made within the driving period and NOT within the rest period.

luck and I hope it serves you