TRAVELTIME Formula – Google Sheets

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.

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.

The TRAVELTIME formula using two cities
Two Addresses

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.

Different Modes of Travel

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.

Four TRAVELTIME formulas with different departure times
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.

The TRAVELTIME formula accepting different geolocation formats
Different Location Formats

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.