RouteMetrics Functions

Custom Excel functions powered by HERE. 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 a single number.

Parameter	Type	Description
`origin`	text	Starting location (address, coordinates, or place name)
`destination`	text	Ending location
`mode`	text, optional	`"driving"` (default), `"walking"`, `"bicycling"`, or `"transit"`
`unit`	text, optional	`"mi"` for miles (default) or `"km"` for kilometers

=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.

Parameter	Type	Description
`origin`	text	Starting location
`destination`	text	Ending location
`mode`	text, optional	`"driving"` (default), `"walking"`, `"bicycling"`, or `"transit"`
`departure_time`	date, optional	Future departure date/time (point at a cell containing a date). Enables traffic-aware driving estimates and transit schedules.

=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.

Parameter	Type	Description
`origin`	text	Starting location
`destination`	text	Ending location
`unit`	text, optional	`"mi"` for miles (default) or `"km"` for kilometers — used for step distances
`mode`	text, optional	`"driving"` (default), `"walking"`, `"bicycling"`, or `"transit"`
`departure_time`	date, optional	Future departure date/time (point at a cell containing a date)

=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.

Parameter	Type	Description
`origin`	text	Starting location
`destination`	text	Ending location
`mode`	text, optional	`"driving"` (default), `"walking"`, `"bicycling"`, or `"transit"`

=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

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. Mixed punctuation ("123 Main" vs "123 Main St") cache as different keys.

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:

What you asked for	Reused for up to	Why
`ROUTE.DISTANCE` and `ROUTE.TIME` (no departure time)	30 days	The distance between two addresses and the typical drive time change only when roads or speed limits do — rarely.
`ROUTE.DIRECTIONS` turn-by-turn steps	7 days	The actual path is more sensitive to construction, closures, and new roads, so we refresh it more often.
Any call with a `departure_time` (traffic-aware)	6 hours	These reflect predicted traffic conditions, which go stale quickly.

Each value automatically refreshes the next time it's calculated after the window above lapses, so your numbers stay current without any action on your part.

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:

Cell shows	Cause
`RouteMetrics: origin and destination are required.`	One of the arguments is blank.
`RouteMetrics: location not found. Use full city + state…`	HERE couldn't geocode the address. Try "City, ST" format.
`RouteMetrics: Sign in required`	Open the task pane and sign in with your Microsoft account.
`RouteMetrics: You've used all of this month's searches…`	Monthly quota exhausted. Buy a credit pack or upgrade.
`RouteMetrics: You've used all your free trial searches…`	The trial's 100 free searches are used up. Subscribe or buy a credit pack to continue.

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.