How to Calculate & Track Your Business Mileage Automatically with Google Sheets

How to set up a Google Spreadsheet to automatically calculate and keep track of your business mileage, ready for your year-end expense claim.

⭐ My Referral Links ⭐
(Commissions/credits are earned from these links, which helps support me and the channel.)

The Spreadsheet formula uses Google Maps' API to plot the driving route between the two locations and then extracts the distance driven. Copy & Paste this into the appropriate cell inside Google Sheets:

[Change C2 and D2 to whichever cells have your start/end points in, and ensure you have a sheet called API_KEY with your API key in cell A1.]

The formula to turn the result of the above into miles is:
=ROUNDUP(CONVERT(E2, "m", "mi"), 1)
[Change E2 to whichever cell it should be referencing.]

⭐🤩 If this video has helped you, a Super Thanks would be very much appreciated! 😘😉

Check out our Amazon Influencer shops!

### Let's connect... ###

Switched On Network is hosted by Paul Freeman-Powell

DISCLOSURE: As an Amazon Associate we earn from qualifying purchases.

Copyright © Innobella Media Limited 2018. All Rights Reserved.

[patsteirer]

So this is great, but what about when you have a list of locations with set miles between them. The mileage tracker isn't taking user inputed values for mileage, it's given a start location and an end location, with a sheet tabulating all possible distances between these locations. How would you create a form to select the starting and ending location, to then be given the mileage between them? The information is on different sheets, and the starting location can be any of the locations, and the ending location, can be any of the locations.

[JRazman]

When you pulled down your Starting Address in C2 with your starting address, it increased the address by 1 in C3

So 10 Downing St, Westminster, London SW1A 2AA became 11 Downing St, Westminster, London SW1A 2AA.

Which may have altered the calculated distance, as it was calculated from the wrong address.

But other wise, nice mileage calculator with Google Sheets.

[gilwoode]

i Thought i ws pretty computer saavy but I cant figure this formula out. i keep getting #N/A ERROR I have a billing account setup

[stevenrussellblack]

Thank you so much for this. Super fast and helped a ton. Thanks mate.

[albulenaberisha]

thank you very much for this video. this was extremely helpful for organizing my business. all the best to you.

[irenejones]

What is the formula for only miles? I don't need meters. Thanks!

[MrOattee]

I had the same formula parse error mentioned here in the comments. It's an issue with copying the formula from your website, once I deleted and typed out manually it worked first time. Hope that helps some people.

[slworking]

Thanks for the help here.
Note: Google now requires HTTPS instead of HTTP. Please update your formula.

[norcalnewsnetwork]

Hello Sir. I officiate baseball in the US. I want to calculate my mileage from work to the game field and then back to my home. What formula do I use for my third column for ending destination? And is this calculation you are providing is it direct straightline or is it actual using google map navagation or turn by turn?

[PotterSpurn]

Thanks this was useful to find for the formulae for low mileage drivers. But could you tell me what is the formula would be for calculating total miles x 0.45 up to 10, 000 miles and 0.25 miles afterwards so that th e calculation for cost auto-changes, once the 10, 000 miles limit arrives on the spreadsheet and the 0.45 pence no longer applies. Surely there is a way of doing this automatically rather than sub-dividing one trip that take you from, say 9, 827 miles to 10, 002 miles, which may be hard to find if you have a long column of total mileages. Thanks.

[rodb]

Thanks for the tutorial. Excellent job. I'm unsure how to track two vehicles on the same sheet and run totals for each vehicle and year end, any suggestions? I'm very new to sheets...this is probably a simple question and solution but wanted to do it the most uncluttered way. Thanks for any help

[soulsteela]

The video is great, clear and very helpful. But what if I go to a few different locations a day? Do I have to do a separate count for each trip? I mean I do 5-10 trips to different locations every day. And I need to put some data from previous months this way, but it'd take me ages.

[joshfarrow]

Can you download trips from a Google calendar into this spreadsheet?

[FacundoRivoir]

Does this formula work if the origin and destination are given as Lat and Long ?

[chloefaaiuaso]

Struggling here. I created the billing account on the console, generated the API key (it says (API Key 1), re-typed the link into notepad and then pasted it into the spreadsheet. No matter what I do, the error says #N/A i.e. "imported content is empty".

Heeelp. Please :)

[RobinMatiucPianoSongs]

Hi mate! Great job you've done with this video!!! Still useful after 3 and a half years!!! Can you please tell me where to look for more parameters at the end of the formula in order to have as results, let's say, "time of travel" or "time to leave ". Thanks a lot!

[jakestr]

Amazing tutorial! Do I have to be specific with the date or can I just add which month the meeting was?

[paulfp]

Whoops, I was too busy talking and didn't notice my funky spelling of the word "Desination"... ;-)

[dannyp]

Its only air miles I think like from zip to zip which probably will short you miles in the long run. I could be wrong though.

[MuhammadUsmanUmerSons]

Somebody helps me with meters formula?