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.
– 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.
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.
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
Gallery of Trucking Spreadsheet
In addition to the image above, you can see a gallery of Trucking Spreadsheet below.
( Click Image to Enlarge )
- Proposal Tracking Spreadsheet
- Keeping Track Of Bills Spreadsheet
- Fundraiser Tracking Spreadsheet
- Downtime Tracking Spreadsheet
Tag; Trucking Spreadsheet, Spreadshee