TRAVELTIME helps spreadsheet users plan trips by finding the time needed to travel between two points. You can calculate the time for hundreds of address pairs in seconds.
❗This custom formula is only available to users of the Travel Formulas add-on in the Google Workspace Marketplace. Travel Formulas uses Google Maps for you while you stay in Sheets.
Contents
Purpose
The TRAVELTIME
function returns the trip duration between locations.
All Travel Formulas
TRAVELTIME – Returns the trip duration between locations
TRAVELDISTANCE – Finds the trip distance between locations
TRAVELROUTEURL – Returns the link to your route in Google Maps™
TRAVELDIRECTIONS – Provides turn-by-turn directions
Syntax
=TRAVELTIME(origin, destination, mode, departure_time)
origin
– The starting location
destination
– The ending location
mode
– The mode of travel (“driving”, “walking”, “bicycling”, “transit”) [optional, defaults to driving]
departure_time
– A datetime in the future for transit/driving in traffic. It is best to use a cell reference formatted to a datetime [optional, defaults to present time]
Examples
Example 1 – Two Addresses
Let’s keep it simple at first and use two cities: one for the origin
and the other for the destination
.
These locations are just a bit oversimplified. But if Google Maps accepts them, the formulas work! It turns out the trip from New York to Los Angeles is way too long at 40 hours and 49 minutes.
This function accepts direct input such as “NY” but can also use cell references. We’ll switch to using cell references for the following examples. Using cell references gives us more flexibility.
Example 2 – Different Modes
We can evaluate not only driving
but also mass transit
, biking
, and walking
.
You could enjoy an Amtrak ride for 64 hours and 44 minutes, bike for 267 hours, or walk for 1011 hours. Let’s bring these two addresses closer and look at different departure times
.
Example 3 – Different Departure Times
Let’s say you’re planning a trip from Chicago to Detroit and want to know the best time to leave. You can use the TRAVELTIME formula and feed it different departure times.
You can save 11 minutes of travel time by leaving at 8 AM instead of 4 PM.
Example 4 – Different Location Formats
You don’t have to use a city name or full address. Other geolocation methods, such as Plus codes and coordinates, also work, as shown below.
Rows 2 through 6 in the image above use the same location but different methods to specify it. You can use a city name, a full address, a placename, plus code, or even latitude and longitude.
Conclusion
The TRAVELTIME formula is a versatile tool that helps you plan your trips without leaving Google Sheets.