Mileage log in Excel: track business miles and work out your claim
A mileage log records every trip with the odometer readings, so you can prove how many miles were for business. Excel adds up the miles per trip type and month and multiplies the business miles by the rate you claim.
In short: one row per trip with date, start and end odometer, type (Business, Commute, Personal), destination, purpose and who you met. Miles are end minus start, totals per type and month come from SUMIFS, and the claim is the business miles times your rate per mile. The rate is an input, because the IRS and HMRC change it, in 2026 even during the year.
Before you start: SUMIF, SUMIFS and percentages. EOMONTH is explained in the exercise.
Try it now
A mileage log stores odometer readings, not distances. The sheet works out the miles for each trip.
Task In D2, calculate the miles driven on the trip from the two odometer readings. The formula is filled down to D8.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Start odometer | End odometer | Miles |
| 2 | 2 Nov 2026 | 48210 | 48224 | |
| 3 | 3 Nov 2026 | 48224 | 48310 | |
| 4 | 3 Nov 2026 | 48310 | 48319 | |
| 5 | 5 Nov 2026 | 48319 | 48333 | |
| 6 | 9 Nov 2026 | 48333 | 48475 | |
| 7 | 12 Nov 2026 | 48475 | 48502 | |
| 8 | 16 Nov 2026 | 48502 | 48563 |
Download the mileage log template
The file has room for 500 trips in an Excel table, miles by formula, a drop-down for the trip type, a red warning when an odometer reading does not follow on, totals and shares per type, a claim with an optional second rate, a Months sheet, ten sample trips and an instructions sheet.
Enlarge previewDownload the finished template
The finished Excel file mileage-log-template.xlsx, with sample rows and instructions, is free. All you need is a free account: no subscription, no payment details.
Create a free account and download the template Already have an account? Log in
How the mileage log is laid out
| Column | Contents | Input or formula in row 9 |
|---|---|---|
| A | Date | Shown as 2 Nov 2026, the same in the US and the UK |
| B, C | Start and end odometer | Readings from the dashboard |
| D | Miles | =IF(OR(B9="",C9=""),"",C9-B9) |
| E | Type | Drop-down: Business, Commute or Personal |
| F | Destination and route | Place, and the route for detours |
| G, H | Purpose, client or contact | For business trips |
Above the table, =SUMIF(Trips[Type],E2,Trips[Miles]) adds up each type, and the share next to it divides by all miles. The list is an Excel table called Trips, so it grows with every new row and has filter buttons in the header.
A conditional formatting rule checks that readings follow on: =AND(ISNUMBER(B10),ISNUMBER(C9),B10<>C9) turns the start reading red when it does not match the end of the row above. Then a trip is missing or a number is mistyped.
Business miles per month with SUMIFS
For a monthly claim you need the business miles in each month. SUMIFS adds them up with three conditions: the type, a date from the first of the month and a date up to the last day, which EOMONTH(G1,0) returns.
Task In G2, calculate the business miles in the month in G1: type Business and a date from the first to the last day of the month.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Type | Miles | Month | 1 Nov 2026 | ||
| 2 | 2 Nov 2026 | Commute | 14 | Business | |||
| 3 | 3 Nov 2026 | Business | 86 | ||||
| 4 | 9 Nov 2026 | Business | 142 | ||||
| 5 | 16 Nov 2026 | Business | 61 | ||||
| 6 | 25 Nov 2026 | Personal | 39 | ||||
| 7 | 2 Dec 2026 | Business | 22 | ||||
| 8 | 7 Dec 2026 | Business | 75 | ||||
| 9 | 30 Oct 2026 | Business | 48 | ||||
| 10 | 14 Dec 2026 | Personal | 31 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
The Months sheet uses the same formula for twelve months and three types, with the table's column names:
=SUMIFS(Trips[Miles],Trips[Type],B$4,Trips[Date],">="&$A5,Trips[Date],"<="&EOMONTH($A5,0))
B$4 is the column header, Business, Commute or Personal, so one formula fills the whole grid. The months come from the year in B2 with =DATE($B$2,1,1) and =EDATE(A5,1).
Business share and personal share
The share of a trip type is its total divided by all miles. With the percent format Excel shows it as a percentage straight away.
The share matters when one car is used for work and privately, because only the business part can be claimed or reimbursed. Fuel, parking and repairs belong in the expense tracker.
Mileage rates: IRS and HMRC
The claim in J5 is the business miles times the rate in J2. Both official rates change, so check them for the dates of your trips:
- US, IRS standard mileage rate for business use: 72.5 cents per mile from 1 January to 30 June 2026 and 76 cents per mile from 1 July to 31 December 2026 (irs.gov: standard mileage rates). If your log spans both halves of 2026, use one log per half year or work out each half separately.
- UK, HMRC approved mileage rates for cars and vans: 55p per mile for the first 10,000 business miles from 6 April 2026 (45p before that date) and 25p for each mile above 10,000. Motorcycles are 24p and bikes 20p per mile (gov.uk: business travel mileage rules).
For the UK two-tier rate, put 10,000 in J3 and the lower rate in J4. The claim then becomes
=IF(N(J3)=0,F2*J2,MIN(F2,J3)*J2+MAX(0,F2-J3)*J4)
MIN takes the business miles up to the limit, MAX the miles above it and never less than zero. With J3 empty, N(J3) is 0 and the plain rate applies. HMRC applies the rates to an employee's business miles for the year, so keep one log per year if you use the two-tier rate. Which trips count as business, and which records you need, depends on your situation; check with your employer or a tax adviser. This page is not tax advice.
Common mistakes
Distances instead of odometer readings
Writing down only "86 miles" hides missing trips. Start and end readings make every gap visible.
Type spelled differently
"business trip" does not match the criteria Business in SUMIF, so the trip drops out of the total. The drop-down prevents it.
Commuting mixed in with business
The template keeps trips between home and your usual workplace as their own type, Commute, so they stay out of the claim. Whether a trip counts as business travel is a tax question, so ask your employer or adviser when in doubt.
Mileage log formulas in German Excel
| English | German |
|---|---|
| SUMIF, SUMIFS | SUMMEWENN, SUMMEWENNS |
| EOMONTH, EDATE, DATE | MONATSENDE, EDATUM, DATUM |
| MIN, MAX, N | MIN, MAX, N |
Which Excel versions support this?
Every function on this page, Excel tables, drop-down lists and conditional formatting work in all current versions of Excel, including Excel for Mac and Excel for the web.
FAQ
Is the mileage log template free?
Yes, with a free account. No subscription and no payment details.
Does it work in kilometers?
Yes. The formulas only subtract readings, so they work with any unit. Rename the Miles header if you like and enter a rate per kilometer.
Can I track several vehicles?
Use one file per vehicle, with the vehicle in B2. That keeps the odometer readings in one unbroken sequence.
How do I find the longest trip?
=MAX(Trips[Miles]) gives the longest trip, and MAXIFS the longest business trip only.
Does it work in Google Sheets?
The .xlsx file opens in Google Sheets, and SUMIFS, EOMONTH and the other functions exist there too. The template is built and tested for Excel, so check the drop-down after the import.
To add up costs per category, the lesson on SUMIF covers the basics.