You’re beginning to plan your long road trip. You open your road atlas and add up the inter-town distances or use your favourite online map app. You want to plan your rest breaks, refuelling places, and overnight stops, and extra time at landmarks or features of interest.
Where to go from here? This page describes a spreadsheet that supports this by requiring only a minimal amount of information to be entered in a Trip sheet, and looking up road speed, vehicle fuel consumption, time zones and other core values from a Reference sheet.
From inter-stop distances it calculates intermediate durations, allowing for road type, breaks and time zone changes, and tallies daily travel time and distance.
Remaining fuel, for each of main and reserve tanks, is also calculated.
You can download these instructions in a pdf file…Road Trips,
and the spreadsheet…Road Trips.
In the following description, reference is made to a trip from Darwin to Brisbane. These cities are nearly 3500km apart by road, with a driving time of about 36 hours. The trip requires multiple travelling days.
Road Trip Spreadsheet
The spreadsheet RoadTrip.xlsx contains three sheets: Trip, Reference and Example. The Trip sheet is the template for road trip planning. Reference contains the lookup tables of vehicle, fuel, road, speed and time zone information. Example demonstrates some of the features of the Trip sheet.
Trip sheet
This is the template for road trip planning. You may enter your trip details into this sheet, but it is recommended to first make a copy and use that. The process to make a copy is shown here.
On your new trip sheet, enter a trip title and brief route details in the top left cells.
Next, select the Vehicle, Towed vehicle (if any) and the Nominated Speed basis. If a towed vehicle is selected, the basis for speed used will default to Towing.
Following the above selections, the sheet will display fuel consumption and range values.
The trip Depart Date is entered, and from this the sheet will display the dates on which daylight saving (DST) starts and finishes in that year.
The sheet is now ready for the trip stop details to be entered. The first few stops for the Darwin to Brisbane trip are shown here.
The minimum requirement is an entry for each of place, time-zone (TZ), distance (Dist) to the next stop, and RoadType. Place and Dist can take any value, but TZ and RoadType must be selected from drop down lists, populated from the Reference tables.
Optional information includes Route Description, Refuel, Break time, number of overnight (ONight) stops, an alternate departure (altDepart) time (the default is in the Times table in Reference) and any other Comments.
An altDepart time from Darwin of 10am is specified, and the RoadType is Open throughout this section. There are Overnight stops at each of Katherine and Tennant Creek, and the vehicle is also Refuelled at each of these. At the other locations there is a specified Break time.
The calculated arrival time (ETA), departure time (ETD), average speed (Av Spd) and Leg Time are in the first four columns to the right. The Av Spd is found from the RoadType and Used Speed values from Reference tables.
The ETD at the current stop is the ETA plus any Break time. If the stop is an overnighter, the ETD is either the default from the Reference sheet, or the alternate Depart time (after the specified number of Nights.)
The ETA at the next stop is the ETD at the current stop plus the Leg Time, which is calculated from the assumed speed and the leg distance, plus offsets caused by Time Zone changes.
In the example, the times for the Barkly Homestead to Camooweal leg automatically add the half hour time zone change as the NT-QLD state border is crossed.
The next three columns to the right display the cumulative daily travel time (Day T) and distance (Day D), and the calculated remaining fuel range (RangeRem.)
Custom formats are applied to provide visual cues to potential issues.
The Day T and Day D columns are shaded green through yellow to red, against criteria specified in the Reference sheet. In this example, the yellow-red thresholds are 8 and 14 hours for time, and 500 and 1500km for distance.
The RangeRem column shows the estimated remaining range, with a level bar to indicate the fuel level: blue for the main tank, red if on reserve, and Empty if no fuel remains.
The cell formatting can reveal information that might not be immediately apparent from the displayed values.
In this hypothetical example, on Day 1 the time stays primarily green while the distance begins to shade orange. This is because the travel is primarily at a high speed.
On Day 2 this is flipped, since the distances are short, but the road types dictate slower travel.
The RangeRem column illustrates the fuel being drawn from the main and reserve tanks on the first day, a refuel on the fifth leg, and then the fuel being drawn from each tank until empty.
On the far right of the trip sheet the total cumulative Trip Dist and Trip Time are displayed.
The trip summary at the top of the sheet shows the Total Distance, Travel Time and approximate Duration, as well as Fuel used and its estimated Cost.
Fuel Consumption
The nominal fuel consumption over each route segment is adjusted for the average speed over that segment. The adjustment generally increases the fuel consumption for speeds both slower and faster than a Best Fuel Speed. All parameters are contained in the Reference sheet.
Reference sheet
There are nine tables in the Reference sheet, which hold common information used in the Trip sheets.
Values in white cells can be changed. As you use the spreadsheet for more and more trips, you will build a library of your own vehicles and associated parameter values. Yellow cells should not be altered, as these contain calculation formulas.
All tables can be extended downwards by typing in the row directly beneath existing values.
Vehicle table
The Vehicle table contains the vehicle list, with the fuel consumption, fuel type, tank capacity and reserve capacity. The reserve capacity may include jerry cans or similar if that is how the reserve fuel is to be carried.
For conventionally fuelled vehicles the unit of capacity is litres, and the consumption is in litres/100km.
For electric vehicles, the tank capacity is set to 1, and the consumption entered as the value of 100/range. This is a fudge that ensures the calculated tank range is correct, as well as enabling a full re-charge for a Refuel and in the Trip sheets.
The SlowFuel and DragFuel are coefficients of a fuel adjustment formula that modifies the fuel consumption for speeds either side of a Best Fuel Speed, defined in the Reference Speed table. The general formula is of the form,
Consumption Ratio ~ SlowFuel/Speed + DragFuel*Speed^2 + Offset
where Offset is determined from the Best Fuel Speed value.
Fuel table
A list of fuel types, and the estimated cost per unit. The unit is also entered.
The unit for an electric vehicle is a re-charge (unsure of how this is costed in practice.)
Towing table
A list of generic towed vehicle types and a percentage rate of increased fuel consumption for each.
Travel Speeds table
Contains a list of road types and three speeds for each type. Posted is the maximum permitted speed, Average is the typical speed for a single vehicle, and Towing is a reduced average speed when towing.
Time Zone table
Lists the time zones for each State as the hour offset, and the daylight savings offset (added to the hour offset) if the State uses DST in the summer months.
It would also be possible to enter time zones by name, such as EAST, EADT, etc. but this would be less intuitive when entering the time zone in the Trip sheet, and would also remove the functionality that automatically adjusts for daylight savings when required.
Daylight Savings table
Allows entry of the rule used to determine the start and end of daylight-saving time each year (in Australia.)
This table is used in conjunction with the Time Zone table to apply correct duration adjustments when travelling across State borders.
Times table
Day Departure is the default time of departure each day. This means that departure times for each day only need to be entered if different from this value.
Day Mid Threshold and Day Hour Limit values are used to set the yellow and red shading profile of the Day T daily time cells in the Trip sheets.
Distance table
Day Mid Threshold and Day Dist Limit values are used to set the yellow and red shading profile of the Day D daily distance cells in the Trip sheets.
Reference Speed table
Best Fuel Speed is the reference speed for the fuel consumption modification formula. The SlowFuel and DragFuel coefficients together with this value (which determines the Offset value) are linked and none should be changed without checking the results independently.
