Tracking Mileage

Many solopreneurs and small businesses don’t take full advantage of the mileage deduction that can offset income each year at tax time. Those that do track mileage know one thing: Small trips add up to significant mileage over a twelve-month period. Like time tracking, though, learning to document your mileage takes some practice so you don’t lose a single mile. Here are some of our favorite tips to help you track your comings and goings.

  • It’s not necessary to do an expense report for every trip. We suggest that you keep a worksheet that lists all of your trips (more on this below).

  • Business mileage doesn’t just mean a client meeting. It can be a trip to Office Depot or a drive to a professional training seminar.

  • It’s always best to log a trip right after you take it, while it’s fresh in your memory. Some people keep a small notebook in the car, then enter the information into their worksheet later on. Others enter the trip directly into the worksheet when they get back. Whatever method works best for you, the key is to log the trip so it’s not forgotten.

  • If you need to reconstruct your trips, start with your credit card statement and look over the charges. You’ll find all of your visits to Best Buy or a coffee shop or parking downtown. All of these may be associated with legitimate business activities. Check the dates against your worksheet to make sure you’ve got them all recorded.

  • Scan your checking account register in case you charged an expense on your debit card that has mileage associated with it.

Here’s how we set up our mileage worksheet.

  • Start a fresh workbook. Start a new worksheet in the workbook and call it “Mileage Log.” This is the sheet where you’ll log your trips. Column A “Date” and format it as a date. Name Column B “Destinations.” Name Column C “Mileage.”

    • When you take a trip, enter the date in column A.

    • In column B, Type in the round trip you made like this:

      • Home > Office Depot 678 Central Ave > Home

      • Office > Starbucks 123 Main Street > Office.

    • Home or Office is the start and end point of the trip. The destination is in the middle.

    • Go to Mapquest or any online mapping tool. Enter the start point and the destination and map it. Take the mileage you see and double it (a round trip) and enter that number into the Mileage column.

Keep entering your trips in the worksheet as you make them. As time progresses, you may make the same trip multiple times (for example, you’ll probably go to the same Office Depot more than once per year).

If you want to automate your mileage worksheet, here are some Excel “nerd tips” the will make entering trips even easier. Trust us…this is cool!

  • Add a second worksheet to the workbook and call it “Mileage Lookup.” Name Column A “Destinations” and make it wide. Type in the round trip using the same format as shown above (for example, Home > Office Depot 678 Central Ave > Home).

  • Using the Mapquest or online mapping tool, enter the round trip mileage in Column B.

  • Go to row 25. Fill cells A25 and B25 with red. This means, “Don’t add any more trips below this line.”

  • Put the cursor on cell A2 (the first entry in your Destinations database). Click the mouse button and drag down to cell B24 (just above the red line). Choose Insert > Name > Define Name and type the word “Destinations” (no quotes) in the Name box. You’ve now created a named range called Destinations.

  • Put the cursor on cell B2 (the first mileage entry in your Destinations database). Click and drag until you get to cell B24. Choose Insert > Name > Define Name and type the word “Mileage” (no quotes) in the Name box. You’ve now created a named range called Mileage.

  • Go back to the Mileage Log worksheet. Click on the “B” column heading so the entire Destinations column is highlighted. Choose Data > Validation. In the Source box, paste this and click OK:

    • ='Mileage Lookup'!$A:$A (This assumes you’ve named your mileage database worksheet “Mileage Lookup”)

    • You’ve now limited the acceptable entries into all of the cells in Column A to the Destinations in your mileage database.

    • On the Mileage Log worksheet, column C Mileage, highlight cell B1. Enter this formula:

    • =LOOKUP(B2,Destinations,Mileage)

    • Copy the formula down a hundred or so rows. Notice how B2 will update itself as the formula moves down the worksheet (B3, B4, etc.) This formula will take the value in the cell to the left (the round trip you chose from the database), find a match in the destinations database by checking the named range “Destinations” and return the value it finds in the Mileage named range.

From this point forward, here’s all you have to do:

  • If this is a trip to a new destination, enter it first in the Mileage Lookup database. We like to keep our trips in alphabetical order, which means you may be inserting a line above an entry, rather than putting it in a blank row below.

  • Go to the Mileage Log worksheet. Go to the next empty row. Enter the date in Column A. When you move the cursor to Column B, you’ll see an arrow on the right side. Click the arrow. You’ll see a list of all of your destinations. Click on one. The mileage will automatically appear in column C.

For a while, you’ll be adding lots of new trips in order to build out your database. Once that’s done, you’ll notice that you’ll be adding fewer and fewer as time goes on. You can use your mileage database year after year, which makes logging mileage really fast.

One caveat. Once you fill in all 25 blank lines on your mileage database, what do you do with entry 26? Find where it fits in alphabetical order and insert a line. The named ranges automatically adjust to include the new row.