How to make a mileage log in Google Sheets that calculates reimbursement

What a mileage log has to record

Whether you’re claiming mileage from an employer or deducting it as self-employed, the record looks the same. IRS Publication 463 (chapter 5) lists what an adequate record of car use shows:

• The date of each trip.
• Where you went: the business destination.
• Why: the business purpose, such as “Client A kickoff” or “Site inspection, 14 Oak St”.
• The miles for each business trip, and the total miles you drove the car in the year, business and personal.

The IRS also says the record should be made at or near the time of the trip. You don’t have to write it down the same day: a log you update weekly counts as timely. What doesn’t hold up well is a list reconstructed from memory in April. A Google Sheet you add to from your phone after each trip is about as timely as it gets, and the edit history (File → Version history) shows when each row went in.

Your employer may want more, such as a project code or a manager’s name. Add those as extra columns; nothing below depends on the column count beyond the ones named.

This guide explains how to keep the record and do the arithmetic. It isn’t tax advice: what you can claim depends on your situation, so check with your employer’s policy or a tax professional.

Set up the Log tab

Make a new spreadsheet with a tab called Log and these headers in row 1. One row per trip, and one trip is one drive from one place to the next. If you go out and come back, that’s two rows, which is also the clearest record.

Three settings make the rest easier:

• Make the Category a dropdown. Select E2:E, choose Insert → Dropdown and add the four options. A typo such as “Busines” would otherwise get a rate of 0 without any error, and you’d never notice.
• Format column A as dates (Format → Number → Date). A date typed as text looks the same but is skipped by every total below.
• Freeze row 1 (View → Freeze → 1 row) so the headers stay put as the log grows.

For places you visit often, type the name the same way every time. “Client A” and “client a ” with a trailing space are different text to a formula, and that matters when you look up miles by place later. A dropdown built from a list of your usual places (a range on a Places tab) keeps them consistent.

A rates tab that changes with the date

Most guides put one rate in a cell. That breaks the moment the rate changes, and in 2026 it changed in the middle of the year. The IRS standard mileage rates for 2026 are (from the IRS standard mileage rates page):

The July increase (Announcement 2026-11) applies to trips on or after July 1, so a June 30 trip and a July 1 trip get different rates even if you log them on the same day. The fix is a small table of rates by start date. Make a tab called Rates:

Keep the start dates in order, oldest first, and add a row whenever the rate changes. When the 2027 rates come out, add a row starting 2027-01-01; the 2026 trips keep their 2026 rates. If you still have 2025 trips to claim, add a row above with 2025-01-01 and the 2025 business rate of 0.70 (medical 0.21).

The header names in row 1 must match your Category dropdown exactly, because the formula finds the column by its header. “Personal” has no column on purpose: personal miles get a rate of 0.

Your employer may use its own rate. Many reimburse at the IRS rate, but some pay less, and a few pay a flat amount per trip. Put their rate in the table instead. Ask what date they apply a new rate from: some switch on the IRS date, some at the start of their next pay period.

The formulas: miles, rate and amount

Put these in row 2 and fill them down (select H2:L2 and drag the small square at the bottom right, or copy them to as many rows as you need). Column K is typed by hand.

H2, Miles, from the odometer readings, rounded to a tenth of a mile:

=IF(G2="", "", ROUND(G2-F2, 1))

The ROUND isn’t just for looks. Spreadsheets store decimals in binary, so 1035.1 minus 1030 comes out as 5.09999999999991. That would show as 5.1 but add up very slightly wrong across a year, and a total like 1,204.79999 looks careless on a claim.

I2, Rate, for the trip’s date and category:

=IFERROR(VLOOKUP(A2, Rates!$A$2:$D$20, MATCH(E2, Rates!$A$1:$D$1, 0), TRUE), 0)

How it works: MATCH finds which column of the Rates tab has the category’s name (Business is column 2). VLOOKUP with TRUE at the end is an approximate match: it finds the last start date that is on or before the trip date and returns the rate from that column. A trip on 2026-06-30 gets 0.725 and one on 2026-07-01 gets 0.76. IFERROR turns two cases into 0: a category with no column (Personal), and a date before the first row of the table.

J2, Amount:

=IF(H2="", "", ROUND(H2*I2, 2))

Round each trip to the cent, the way you’d write it on a claim form, so the total you submit matches the rows exactly.

L2, Total with tolls and parking:

=IF(J2="", "", J2+N(K2))

N(K2) treats an empty toll cell as 0. Tolls and parking for a business trip are claimed on top of the mileage rate, so they get their own column instead of being added to the miles.

Here is what a few rows look like with real numbers:

The same 12.4-mile drive is worth 8.99 on June 30 and 9.42 on July 1. That’s the whole reason for the rates tab.

Prefer one formula per column? If rows are added by a form (see below) or you don’t want to fill down, put a single array formula in row 2 and leave the rest of the column empty. For Miles in H2:

=ARRAYFORMULA(IF(G2:G="", , ROUND(G2:G-F2:F, 1)))

For the Rate in I2, the column-finding MATCH doesn’t spread down a column reliably, so this version names each category instead. If you add a category, add one more line like these:

=ARRAYFORMULA(IF(A2:A="", ,
  IFERROR(VLOOKUP(A2:A, Rates!$A$2:$D$20, 2, TRUE), 0) * (E2:E="Business")
+ IFERROR(VLOOKUP(A2:A, Rates!$A$2:$D$20, 3, TRUE), 0) * (E2:E="Medical")
+ IFERROR(VLOOKUP(A2:A, Rates!$A$2:$D$20, 4, TRUE), 0) * (E2:E="Charity")))

An array formula fills the whole column, so typing anything in a cell below it gives a #REF! error in row 2. Clear the typed cell and it comes back.

Catch odometer mistakes

Odometer logs go wrong in two ways: a reading typed wrong, and trips that were never logged. If you log every drive, personal ones included, each trip should start where the last one ended. Sort the log by date, then by start odometer (Data → Sort range), and add a Gap column, M, starting in row 3:

=IF(OR(F3="", G2=""), "", ROUND(F3-G2, 1))

• 0 means the trips join up.
• A positive number is miles you drove but didn’t log. If you only log business trips, those are your personal miles in between, which is expected.
• A negative number is a mistake: the trip starts before the last one ended. Usually a typo, or rows out of order.

To highlight the mistakes, select M3:M, choose Format → Conditional formatting, pick Less than, type 0 and choose a red fill. Add a second rule on H2:H for “Less than 0” to catch a start and end reading typed the wrong way round.

For the total miles you drove in the year, which the IRS record asks for alongside business miles, the simplest number is the odometer on January 1 and December 31. If you only log business trips, write those two readings in a note at the top of the log each year. If you log every drive, the log already has them:

=MAX(Log!G2:G)-MIN(Log!F2:F)

Use this on a log that holds one year and one car. With two cars, give each its own Log tab, or add a Vehicle column and keep the readings apart.

Monthly and yearly totals

Make a tab called Summary. In A2 type the first day of the year, 2026-01-01, and in A3:

=EDATE(A2, 1)

Fill A3 down to A13 for the twelve months, and format column A as Format → Number → Custom date and time with just the month and year. Then business miles for each month in B2:

=SUMIFS(Log!$H:$H, Log!$E:$E, "Business", Log!$A:$A, ">="&A2, Log!$A:$A, "<"&EDATE(A2, 1))

And the business amount in C2, the same formula on column J:

=SUMIFS(Log!$J:$J, Log!$E:$E, "Business", Log!$A:$A, ">="&A2, Log!$A:$A, "<"&EDATE(A2, 1))

Fill both down to row 13. The two date conditions take every trip from the first of the month up to, but not including, the first of the next month, so a trip at the end of the month is never counted twice or missed. Change "Business" to "Medical" or "Charity" in extra columns for those totals.

For the year, the share of miles that were business tells you (or your accountant) the business-use percentage of the car. Business miles in F1, all logged miles in F2, the share in F3:

=SUMIFS(Log!$H:$H, Log!$E:$E, "Business", Log!$A:$A, ">="&DATE(2026,1,1), Log!$A:$A, "<"&DATE(2027,1,1))
=SUMIFS(Log!$H:$H, Log!$A:$A, ">="&DATE(2026,1,1), Log!$A:$A, "<"&DATE(2027,1,1))
=IF(F2=0, 0, F1/F2)

Format F3 as a percentage. If you don’t log personal trips, use the odometer total from the section above in F2 instead of the logged miles, or the percentage will look like 100%.

You can also use Insert → Pivot table on the Log tab, with Date in rows (right-click a date, Create pivot date group → Year-Month), Category in columns and Miles summed as values. It’s quicker to set up; the formulas are easier to copy into a claim form.

A reimbursement report for one month

When it’s time to claim, you need just that month’s business trips. Make a Report tab, put the first and last day of the period in B1 and B2, and in A4:

=FILTER(Log!A2:L, (Log!E2:E="Business") * (Log!A2:A>=B1) * (Log!A2:A<=B2), "No trips")

Each condition is TRUE or FALSE for every row; multiplying them keeps only the rows where all three are true. Copy the Log headers into row 3 above it, and put the claim total in D1:

=SUM(FILTER(Log!J2:J, (Log!E2:E="Business") * (Log!A2:A>=B1) * (Log!A2:A<=B2)))

Use column L instead of J if your employer reimburses tolls and parking on the same claim. To send it, select the report and choose File → Download → PDF, with Selected cells under Export, or share the tab view-only. Change B1 and B2 and the report rebuilds for the next month.

Log trips from your phone

The log is only as good as your habit of filling it in, and a spreadsheet is fiddly on a phone. A Google Form fixes that, and it’s free:

1. In the spreadsheet, choose Tools → Create a new form.
2. Add questions: Date (a Date question), From and To (Dropdown, with your usual places, or Short answer), Purpose (Short answer), Category (Dropdown), Start odometer and End odometer (Short answer, with Response validation → Number).
3. Open the form’s link on your phone and add it to your home screen. One tap after each drive.

The answers land on a new tab called Form Responses 1, one row per trip, with a Timestamp in column A that doubles as proof the trip was recorded when it happened. The columns are in the order of your questions, so they’re shifted one to the right of the Log layout above: Date in B, From in C, and so on.

Two things to know:

• New form rows don’t copy your formulas. Use the ARRAYFORMULA versions from above, placed in the first empty columns to the right of the form’s answers, and point them at the shifted columns (odometers in G and H, for example). They fill each new row as it arrives.
• Don’t insert or delete columns inside the form’s answer columns. The form keeps writing to the positions it knows. Add your formula columns to the right of the last answer column.

If you’d rather keep the Log tab as your main record, make it read the form: in Log!A2, =FILTER('Form Responses 1'!B2:H, 'Form Responses 1'!B2:B<>"") brings the answers across in the Log column order, as long as your form questions are in the same order as the Log headers.

No odometer? Miles from addresses

Plenty of people don’t note the odometer, and many employers accept map-calculated miles for a known route, such as office to a client. (For tax purposes the IRS wants the mileage of each business use; odometer readings are the strongest evidence, so if you have them, keep them.) Two warnings before you start:

• Use driving distance, not straight-line distance. A straight line between two addresses is always shorter than the drive, often by a fifth or more in a city. It’s fine for “which store is nearest”, but it undercounts a mileage claim.
• Freeze the miles once they’re in. A route can change when roads change. A trip you made in March should keep March’s miles. After the miles appear, copy them and use Edit → Paste special → Values only.

The free route: our driving distance guide has a short Apps Script function, =DRIVE(start, end), that asks Google Maps for the miles and minutes between two addresses. Paste the script in once (Extensions → Apps Script), and then, with addresses in B and C:

=INDEX(DRIVE(B2, C2), 1, 1)

DRIVE returns miles and minutes side by side; INDEX(…, 1, 1) keeps just the miles. Apps Script allows about 1,000 route lookups a day on a personal Google account, which is plenty for a log.

Better: a Routes tab for trips you repeat. Most people drive the same dozen trips over and over. Look each one up once, and the log reuses the answer. Make a Routes tab with From in A, To in B and one-way miles in C:

Fill C from =DRIVE() (with the place names swapped for addresses from your Places tab) or by reading the route off Google Maps, then paste as values. In the log, Miles in H2 becomes:

=LET(fwd, SUMIFS(Routes!$C:$C, Routes!$A:$A, B2, Routes!$B:$B, C2),
  IF(fwd > 0, fwd, SUMIFS(Routes!$C:$C, Routes!$A:$A, C2, Routes!$B:$B, B2)))

It looks the trip up in the direction you drove it, and if that isn’t in the table, the other way round, so “Client A → Office” uses the Office → Client A row. A trip that isn’t in the table at all shows 0, which is your cue to add the route. (Driving distances can differ a little by direction because of one-way streets and exits. If the difference matters to you, list both directions.)

The Rate, Amount and Total formulas don’t change: they only need the miles in column H.

An add-on: MapRows

If you have a column of trip start and end addresses and don’t want to keep a script, MapRows Pro adds driving, walking and cycling distance and travel time from a sidebar. You pick the Origin and Destination columns (addresses, or lat, lng in either), choose miles or km, and it writes the distance, minutes and a readable time into new columns, with a status per row so you can see any address it couldn’t find. It works row by row, one origin and one destination per row, which is exactly the shape of a mileage log.

The numbers it writes are plain values, not formulas, so they don’t change later, which is what you want for a record of past trips.

Everything else in this guide (the rates tab, the totals, the report and the form) works the same with or without it.

What counts, and what doesn’t

A tidy log of trips that don’t qualify won’t help. The common rules, from IRS Publication 463:

• Commuting doesn’t count. Driving between home and your main or regular place of work is personal, however far it is, even if you take work calls on the way. Driving from the office to a client and back does count. Log commutes as Personal if you log every drive.
• Tolls and parking for business trips can be claimed in addition to the standard mileage rate. Parking at your regular workplace can’t.
• Employees and taxes: under current law, employees can’t deduct unreimbursed work mileage on their federal return, so for most employees the log is for claiming reimbursement from the employer. Self-employed people deduct business mileage on their business return.
• Reimbursement above the IRS rate is generally treated as taxable pay for the excess, which is one reason employers tend to use the IRS rate.
• Medical and charity miles have their own rates (in the Rates tab above) and their own rules for when they can be deducted.
• Keep the records. Keep the sheet (and a yearly PDF copy) for as long as your tax records for that year; Publication 463 covers how long.

Rules change, and these are summaries. If a large claim depends on one of them, check the current publication or ask a tax professional.

Common problems

The rate is 0 for a business trip. Either the category doesn’t exactly match a header on the Rates tab (a typo or a trailing space; a dropdown prevents this), or the trip date is earlier than the first row of the Rates tab. Add a row for the earlier year.

A monthly total is 0, or smaller than the rows add up to. Some dates are text, not dates. Test one with =ISNUMBER(A2): FALSE means text. Retype it, or select the column and use Format → Number → Date; for a whole column of text dates, =DATEVALUE(A2) in a spare column converts them, then paste back as values. The same applies to odometer readings typed with a stray space or unit (“1012 mi”).

Negative miles. The start and end readings are swapped, or the odometer rolled over to 0 on an old car. Fix the reading; the Gap column flags the neighbouring rows.

The rate looks one period off. The Rates tab dates aren’t in order (the approximate match needs oldest first), or a start date is typed as text. Sort the Rates tab by column A.

Commas and decimals the wrong way round. In a spreadsheet set to a European locale, formula arguments are separated by semicolons and 0.725 is written 0,725. Change File → Settings → Locale to United States, or swap the separators.

#REF! at the top of an array column. Something was typed into a cell the array formula wants to fill. Clear it.

DRIVE shows NOT_FOUND or a blank. Google couldn’t place one of the addresses. Add the city and state, or the ZIP code, and it usually resolves. The driving distance guide covers the other messages.

Frequently asked questions

Related

• Calculate driving distance and travel time between addresses in Google Sheets
• How to geocode addresses in Google Sheets
• How to find the nearest location in Google Sheets
• MapRows documentation
• All guides

Disclosure: we make MapRows, the Google Sheets add-on mentioned in this guide. Every formula and the script here work without it.

Google Sheets and Google Maps are trademarks of Google LLC. ZIP Code is a trademark of the United States Postal Service. MapRows and Cellbrook are not affiliated with or endorsed by Google, the U.S. Census Bureau or the Postal Service.