RouteMetrics Functions

All functions are namespaced under ROUTE. Type =ROUTE. in any cell to see them in IntelliSense.

Watch the tutorial video for a full walkthrough, or jump straight to a function below.

ROUTE.DISTANCE(origin, destination, [mode], [unit])

Trip distance between two locations. Returns the number of hours.

=ROUTE.DISTANCE("Boston, MA", "New York, NY")                  →  217.33
=ROUTE.DISTANCE("Boston, MA", "New York, NY", "driving", "km") →  349.76

ROUTE.TIME(origin, destination, [mode], [departure_time])

Trip duration in minutes, rounded to one decimal. Returns a single number.

=ROUTE.TIME("Boston, MA", "New York, NY")                 →  223.7
=ROUTE.TIME("Boston, MA", "New York, NY", "transit")      →  281.0
=ROUTE.TIME("Boston, MA", "New York, NY", "driving", A1)  →  247.3   (A1 = tomorrow 8:00 AM)

ROUTE.DIRECTIONS(origin, destination, [unit], [mode], [departure_time])

Turn-by-turn directions as a spilled column, with the distance of each step. Drop it into one cell and the steps fill the cells below.

=ROUTE.DIRECTIONS("Boston, MA", "New York, NY")
  →  1. Head west on I-90 W (12.4 mi)
     2. Continue onto I-84 W (41.6 mi)
     3. ...

ROUTE.URL(origin, destination, [mode])

A clickable Google Maps directions URL between two locations. Returns text. No API call — this function never consumes a search.

=ROUTE.URL("Boston, MA", "New York, NY")
  →  https://www.google.com/maps/dir/?api=1&origin=Boston%2C+MA&destination=New+York%2C+NY&travelmode=driving

ROUTE.STATEMILES(origin, destination, state, [mode])

Truck miles a single trip drives in one state or province — the number IFTA fuel-tax returns are built on. Returns a single number, so you can copy it down a column: put one trip per row and a column for each state you run. Returns 0 when the route never enters that state.

=ROUTE.STATEMILES("Pittsburgh, PA", "Cincinnati, OH", "OH", "truck")  →  231.1
=ROUTE.STATEMILES("Pittsburgh, PA", "Cincinnati, OH", "PA")           →  45.3

Truck miles come from HERE truck-legal routing and are estimates for planning and IFTA prep, not a certified mileage source.

ROUTE.IFTA(origins, destinations, [mode])

A per-state mileage summary across many trips, in one formula. Point it at your From and To columns and it spills a two-column table — every jurisdiction your trips crossed and the total miles in each, sorted by miles. Blank rows are skipped; one billed search per origin/destination pair. Because it spills, use it in a single cell — don't copy it down (each state column of a per-trip breakdown is what ROUTE.STATEMILES is for).

=ROUTE.IFTA(A2:A100, B2:B100, "truck")
  →  Jurisdiction   Miles
     OH             616.5
     IN             193.7
     PA             101.7

If some trips can't be calculated (a bad address, or your searches running out partway), the totals only cover the trips that worked — so the table ends with an “Incomplete: N of M trips” row telling you how many are missing. Fix those rows and recalculate before filing.

Accepted input formats

• Full addresses: "1600 Amphitheatre Pkwy, Mountain View, CA"
• City + state (most reliable for fuzzy input): "Austin, TX"
• Coordinates: "40.7128,-74.0060"
• Airport codes: "LAX", "JFK"
• Place names: "Eiffel Tower"

Tip: When fill-down accuracy matters, normalize to "City, ST" format.

Caching and freshness

Excel persists custom function results in the workbook file. When you close and reopen a workbook, cells with prior values do not re-fire — they keep their last computed value, and no searches are consumed.

A formula recalculates — and counts as a search — when its input cells change or when Excel runs a recalculation. Server-side caching keeps those responses fast and reduces cost on our side, but each function call still draws against your quota.

Tip: spend searches deliberately. Build out your spreadsheet and let it settle; avoid forcing full-sheet recalculations you don't need.

How current are the results?

To keep things fast, we briefly remember each lookup on our servers and reuse it for repeat calls. How long we hold a result depends on how quickly that kind of value actually changes in the real world — distances between two fixed points barely move (held the full 30 days our map provider allows), while live traffic changes by the hour:

Cached values are reused until the window above lapses. Because these are standard Excel functions, a cell keeps its last result until Excel recalculates it — editing an input, or pressing Ctrl+Alt+F9 to force a full recalculation, will fetch fresh data once the window has passed.

Common errors

When a function can't complete, the cell shows a descriptive error message as text instead of the expected number / URL / list. The text starts with RouteMetrics: so you can spot it at a glance. Common messages:

Heads up: aggregating a column that contains an error message (e.g. =SUM(B2:B100)) returns #VALUE! for the total — which is the right behavior, since silently summing past an error would give you a wrong number.