How to Calculate & Track Your Business Mileage Automatically with Google Sheets
Vložit
- čas přidán 6. 09. 2024
- 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 ⭐
🏎️ Schedule a FREE test drive or order Tesla Products: geni.us/SoN-TESLA
💡 Split £100 with me when you switch to 🐙 Octopus Energy: geni.us/SoN-Oc...
🧋 Get £10 off at Huel: geni.us/SoN-Huel
🎸 Buy musical instruments, audio & PA gear from Thomann: geni.us/SoN-Th...
🌍 My KIT profile: kit.co/paulfp
(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:
=importxml("maps.googleapi..." & C2 & "&destination=" & D2 & "&sensor=false&alternatives=false&key=" & API_KEY!$A$1,"//leg/distance/value")
[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! 😘😉
► Support on PATREON: / switchedonnetwork
► Browse our MERCH: teespring.com/...
► Explore our GEAR: kit.co/paulfp
Check out our Amazon Influencer shops!
► UK: www.amazon.co....
► USA: www.amazon.com...
Let's connect... ###
Twitter - / switchedonnet
Facebook - / switchedonnetwork
Instagram - / switchedonnetwork
Tumblr - / switchedonnetwork
Pinterest - www.pinterest....
Visit our website at www.switchedon... and enable push notifications so you never miss a new video or post.
Switched On Network is hosted by Paul Freeman-Powell
► Follow Paul on Twitter: / paulfp
► Follow Paul on Facebook: / paulfp
We sometimes use free sound effects in our videos from www.freesfx.co.uk
Stock video clips are (usually) from VideoBlocks, now called Storyblocks Video. You can get a free trial at videoblocks.go...
DISCLOSURE: As an Amazon Associate we earn from qualifying purchases.
Copyright © Innobella Media Limited 2018. All Rights Reserved.
www.innobellam...
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
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.
Thanks for the help here.
Note: Google now requires HTTPS instead of HTTP. Please update your formula.
Ah yes, thanks! That was actually a typo on the website (just checked my own sheet and I've been using https) so that's been updated now. Thanks for pointing that out!
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.
+Shayne S well spotted, oops! :)
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.
aargh! Yes, it's converted the quotes into fancy ones again. I fixed that a while ago but it must have messed up again. I'll go and fix again now. Cheers!
@@SwitchedOnNetwork happy to help.
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 :)
And the Directions API is enabled.
thank you very much for this video. this was extremely helpful for organizing my business. all the best to you.
You're very welcome!
Does this formula work if the origin and destination are given as Lat and Long ?
You'll have to adjust slightly I think
How to set up the API sheet?
Whoops, I was too busy talking and didn't notice my funky spelling of the word "Desination"... ;-)
🤪
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.
Yeah I do each trip as a separate entry regardless of the date.
Not working now I guess. When using the formula, an error comes up stating Imported content is empty.
Have you got an API key?
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
Glad it was useful! The easiest way to do 2 vehicles and keep totals separate etc. would be to duplicate and then rename the sheet (on the tab at the bottom toolbar) so you can run 2 side by side for each year, and name them YE22 Car 1, YE22 Car 2 etc.
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?
Yes, it grabs the total distance from a driving route from the turn by turn directions from Google Maps navigation. The best way would be to start at place A, set place B as a stop and then place C as the destination (so just 1 API call). You'll have to look up the API documentation to see exactly how to do that and then change the code accordingly.
Link to webaite goes to youtube channel... so can not copy formula
Ah yes sorry about that, I was having problems with the website so had to (temporarily) take it down. Hope to get it back up soon.
Can you download trips from a Google calendar into this spreadsheet?
I imagine you probably can. Export events as a CSV, manipulate a bit in Notepad++ and then upload to Sheets. Not tried it myself so don't know the full details but yeah give it a go!
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.
I think you'd need more than just a single formula, probably have to write a function or something. I don't drive that much for business at the moment so no need for me 😂
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.
No, this method looks at a driving route suggested by Google Maps and gets the distance from that. Definitely not "as the crow flies" - you can check the distances by going to Google maps and they should be the same 👍
You're right. Zip code to zip code and google chooses the zip. Not address to address, but it beats typing it all in if it's no big significance
Thank you so much for this. Super fast and helped a ton. Thanks mate.
My pleasure, glad it was useful!
This is not working for me at all. API Key on API sheet, copied the formula from this video and it still doesn't work. Can you help?
N/A imported content is empty
Add another cell in your sheet so that it outputs the URL that's generated by putting the start/end points plus API key in. Then copy/paste that into a web browser and you'll see the full error message which will explain what's going wrong and you'll (hopefully) be able to clearly see what you need to do to fix it. Let me know how you get on!
Amazing tutorial! Do I have to be specific with the date or can I just add which month the meeting was?
Thanks! Answer to your question depends on the tax office in your country I guess and what level of record keeping they would require. I can't give advice on that but I record the exact date because why not? 🤣
This did not work for me. Does the address have to be typed in some way?
I have an issue with the API. URL that shows in Google Maps in the formula shows an error
Get the sheet to print the entire URL to a cell that you can copy/paste into a web browser then you'll be able to see the full text of the error and debug.
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!
Do you mean bits of data like you'd see in the Google Maps web app, where you can tell it you'd need to arrive by a certain time, etc.? I'm not sure if/how they're accessible via the API with this method as I've never tried it, however if you paste the URL that's generated into a web browser you can view all the XML that comes back and see if there's anything useful for your needs. Good luck!
@@SwitchedOnNetwork Hi there! Thanks a lot for the tip, it worked! I found the Travel Time...here is the Google Sheets formula: =importxml("maps.googleapis.com/maps/api/directions/xml?origin=" & C2 & "&destination=" & D2 & "&sensor=false&alternatives=false&key=___YOUR_API_KEY___","//leg/duration/text")
awesome, glad it worked!
I guess that could be really useful if you charge clients for time spent travelling as well as fuel cost :)
What is the formula for only miles? I don't need meters. Thanks!
This is it; I didn't need metres either but that's how the Google maps API works - it gives you the distance of a journey in metres so then that's converted into miles for the purposes of the spreadsheet with the formula I used.
This is the formula to convert:
=ROUNDUP(CONVERT(xx, "m", "mi"), 1)
(Where xx is the cell)
Somebody helps me with meters formula?
Please make an update for 2022 for this, I could not get it to work. Maybe I am doing the API wrong...?
What's happening? Have you got an API key set up and enabled billing on the Google Cloud account?
3 trips, one-way. Not to and from. That would be 12 miles. Great video, but I don't want metres on my chart. How can i do this with miles only?
It's part of the working so it has to get the value in metres... you could probably rewrite the formula so it doesn't have to store it in a cell but that would make it more complicated. You could shove the column 1000 cols to the right so it's out of the way and still reference it I suppose!
you could always hide the metres column
What if I use feet!
Switch to metric and be free of nonsense units! 😉😜
Hi. I'm trying to integrate this into a chain where I use Google Forms, where I can quickly input a job on the fly. The data I put in is captured on Google sheets. On that google sheet I have second tab which simply ='s any data that arrives on the form input tab. The second tab has columns to work out the mileage calc. When I use the code from the website I get the error message: #N/A - Error The imported Content Is Empty. I tried referencing the input tab directly but that didn't seem to work either - Please, is there a way?
So are you trying to grab a value from a cell in one sheet and use it in a formula in another, with the !SHEETNAME method?
@@SwitchedOnNetwork Yes (I think so) I'm self taught so don't always know what I've done :) . Essentially I wanted to keep the data from the form unedited. So on Tab2 I hit "=" with cell A1 selected, switched to Tab1 (where form data is recorded) and selected A1. Switch back, drag so any tab1 data will be replicated in tab2 when I fill in the google form. In tab2 is an extra column to calculate mileage (by looking automatically at the ='Form responses 1'!C6' cell for job address column and comparing it to my home address) using your method. But I think its the API key thing - I couldn't actually find an explanation of that - I was hoping to create something that would be free/simple. API keys (whatever they are) might be a bridge too far.
The formula I copied from your website says error when I load it into the spreadsheet.
Yeah it does that for me sometimes too. If you try again later, it should work. I'm not sure why the maps API seems temporamental, I've not debugged it... but as long as you've got the main data saved it'll calculate it when it works 👍😀
I am having issues on creating the API directions. After completing the all the tasks I am getting the #name? error. Any ideas why?
My advice would be to debug it step by step from the start of the process, so get Sheets to output the full URL that it's querying Google Maps with and then paste that into a web browser yourself. At that point you may be able to spot the error just by looking at how the URL has been constructed but if not, you will get a more helpful error message in the browser. Good luck!
@@SwitchedOnNetwork so let me tell you how stupid I was today. I was imputing everything into excel vs sheets. Its amazing how this works when the data is put into the proper program. Now with all the stupidness out of the way is there a way to calculate miles from and to a location with several stops in the middle without adding the extra address to each line?
Haha oops!
Hmm no I don't think so, you'd need an address for each waypoint at least
i have cordinate not a adress. it doesnt work. can you please help me
Convert to address? Or modify the code to accommodate coordinates instead...
@@SwitchedOnNetwork can you please share your contact info so i can contact you. Please guide me
can you put the template up for download
The formula you need can be copied and pasted from this page here: www.switchedonnetwork.com/2018/01/25/how-to-automatically-calculate-track-your-business-mileage-with-google-sheets/
hope that helps! Let me know how you get on :)
Guys i need a km formula for example i right 23 in a cell and when i hit enter it shows 23 Km anyone can help me
You mean just just want to add the text " km" after the number value?
Just copy and past the formula? This did not work
Are you getting an error? Did you create an API account and have you set up your API key? (The process changed after this video was made, annoyingly, so you now need a billing account set up.)
Hi thank you for sharing this awesome way to keep track of the mileage. I don't know if anything changed, but the formula doesn't seem to work. I had followed your step on your website and even waited for 24 hours to check again. And it's still not working. Any idea?
Glad you like it! Yes there has been a change since I made the video - you must now with each request to the API with an access token and also have a billing account set up with Google. Don't worry, your free allowance should more than cover it. More details on the website...
@@SwitchedOnNetwork Hi thank you again for getting back to me. I was reading the comments on your website for trouble shooting. But I have a hard time understanding them since I don't know anything about coding and bad at using the google sheet... Would you have step by step instructions? Sorry, if it's too inconvenient I understand.
@@lemomentcapturer I am having the same issue here. Might just calculate my mileage the old school way instead of using the API formula
@@emilygrant5486 LOL! Yeah I finally gave up and went back to the old fashion way as well!!
Ooh no! It does work, I use it every time. What's the issue? I'll try to help 😀
Do you need to pay for the Google API?
You need to set up a billing account (you didn't when I made the video) but your free credit allowance will more than cover this.
anybody else getting #ERROR Formula parse error? i've set up and tested my key...
same here. did you find out how to fix?
Have you created a (free) billing account with Google, activated the maps API and put the API key in your spreadsheet? The process changed slightly since this video was made but it still works perfectly - I use it for my own business miles. Let me know how you get on 👍
@@SwitchedOnNetwork Yeah I have a paid google dev api account... not sure whats going on... there is a lot of stuff on google dev what needs to be activated exactly?
Thanks for your reply btw:)
@@chrisjennings5047 OK so make sure it's definitely an "API Key" you've generated under Credentials, and not any of the other options. Then it's the Directions API you need to enable for that project.
Finally, the API key needs to be passed in the URL to the api endpoint inside your formula, like this: &key=xxx
I created another sheet in my document and pasted the API key in there, so that I can reference it in the formula like this:
&key=" & API_KEY!$A$1
You should be up and running now - let me know!
How to receive the discount code? I can't locate your email
Hi - you can send me a message on either Facebook, Twitter or Instagram. Search for Switched on Network or look for the links on the channel or in video descriptions, and I'll send you a discount code :)
This is amazing
dam. code says #N/A. Did the code break? Thanks
Thanks, hope you find it as useful as I do!
@@SwitchedOnNetwork I figured it out! Working great now
So you drove 8 miles and want 3.51 from the tax man. fuel price to cover 8 miles will be less than 3.51. how does it work? what am i missing here?
It depends where you are in the world, but in the UK the tax authorities allow you 45p per mile plus 5p for each passenger. It covers all costs like wear and tear too, not just fuel.
@@SwitchedOnNetwork i am in uk. Just looks a bit strange if i spent about 800 on petrol and claim more than 2k back. Where does it go? Under expenses or another field? It's my first time 😂
Thx.
Here's the info direct from the horse's mouth: www.gov.uk/expenses-and-benefits-business-travel-mileage/rules-for-tax
Code doesn't work. I keep getting an error :(
Have you set up your API key correctly? If you construct the URL and then paste into a web browser you'll be able to see the exact error message and see what's going wrong.
The key to this is setting up the Goggle API. Without that it doesn't work. But how do
mere mortals get the API?
You can just sign up, no heavenly credentials required 👍
Google maps gives distance in miles if u ask what is the miles from point A to B. Its accurate and verifiable by the IRS in case of audit. This guy over complicates his life smh.
Hmm, how is this overcomplicating things? I find it really easy and still use it now - bung in where I went, drag the formula from the line above down, and it's all done. Then at the end of the year I have a figure to claim. I'm always keen to make things even simpler though - what are you suggesting? :)
@@SwitchedOnNetwork Hey whatever works, im always looking for new ideas just a new business owner approaching 10 year anniversary yet STILL learning so I'm no expert on anything however I'm not doing all that. Didn't mean to be critical, not saying it's wrong
🤯🤯🤯🤯🤯
Thank you. It is really helpful.
I struggled for a bit figured it out finally.
For KM:
=importxml("maps.googleapis.com/maps/api/directions/xml?origin=" & C3 & "&destination=" & D3 & "&sensor=false&alternatives=false&key=___Your Key____","//leg/distance/text")
Great, glad you got it sorted 👍👍
@@SwitchedOnNetwork You should make a working excel version and monetize it :)
which key ...?
whether the Google API key...?
You need to sign up for one in the Google Cloud developers console