The core Excel formula for mileage calculation is simple: =EndOdometer - StartOdometer for distance, or =MilesDriven * RatePerMile for reimbursement. For a full log, you can combine these in a table with =IF(B2="","",B2-A2) to auto-calculate each trip’s miles. This article explains the formulas, how to set up a mileage tracker, and how to handle common issues like date ranges and reimbursements.
Why You Might Need a Mileage Formula
Mileage tracking is common for business expense reports, tax deductions (if you’re self-employed), or simply monitoring vehicle usage. Excel is a flexible tool because you can store raw data (odometer readings, dates, purposes) and let formulas do the arithmetic. Unlike a paper log, a spreadsheet lets you sum totals, filter by month, and apply different reimbursement rates without re-entering numbers.
The Basic Distance Formula
The most straightforward calculation subtracts the starting odometer from the ending odometer. Assume you have:
- Column A: Starting Odometer (e.g., A2:A100)
- Column B: Ending Odometer (e.g., B2:B100)
- Column C: Miles Driven (where you put the formula)
In cell C2, enter:
=B2-A2
This returns the difference. For example, if A2 is 10,000 and B2 is 10,250, the result is 250 miles. Drag the fill handle down to copy the formula to other rows. If you want to avoid showing a result when a row is incomplete, use an IF formula:
=IF(OR(A2="",B2=""),"",B2-A2)
This checks if either cell is empty and leaves the cell blank if so. Otherwise, it calculates the difference.
Calculating Mileage Reimbursement
To calculate reimbursement, multiply miles by a rate. For example, the IRS standard business mileage rate for 2025 is 70 cents per mile (but always verify the current rate, as it changes annually). In your table, add a column for Rate (e.g., Column D) and a column for Reimbursement (Column E). If you have miles in C2 and rate in D2, use:
=C2*D2
For a fixed rate, you can hardcode it: =C2*0.70. To sum total reimbursement for a period, use =SUM(E2:E100). For a monthly total, you might use =SUMIFS(E2:E100, A2:A100, ">="&DATE(2025,1,1), A2:A100, "<="&DATE(2025,1,31)), assuming your date column is column A (adjust as needed).
Handling Date Ranges and Monthly Totals
If you want to calculate miles driven within a specific date range, you need a date column. Suppose you have:
- Column A: Date (e.g., A2:A100)
- Column B: Starting Odometer
- Column C: Ending Odometer
- Column D: Miles (formula:
=C2-B2)
To sum miles for January 2025, use:
=SUMIFS(D2:D100, A2:A100, ">="&DATE(2025,1,1), A2:A100, "<="&DATE(2025,1,31))
This works in Excel 2010 and later. For a more dynamic approach, you can use a cell for the start date (e.g., F1) and end date (F2):
=SUMIFS(D2:D100, A2:A100, ">="&F1, A2:A100, "<="&F2)
If you have dates stored as text, this won't work—ensure they are real dates (format as Date).
Using a PivotTable for Mileage Analysis
If you have a large log, a PivotTable is an excellent way to summarize miles by month, purpose, or vehicle. Select your data (including headers), go to Insert > PivotTable, and place the date field in Rows, the miles field in Values (as Sum). Then group the date field by months: right-click a date in the PivotTable, choose Group, and select Months. This gives you a monthly mileage total without writing complex formulas.
Common Mistakes and How to Fix Them
- Odometer rollover: If your vehicle's odometer resets (e.g., after 999,999), the subtraction won't work. Use a formula like
=IF(B2if the max reading is 1,000,000, but verify your vehicle's odometer limit. - Blank cells: The IF formula above prevents errors. Without it, a blank cell is treated as 0, giving a negative result.
- Text numbers: If your odometer values are stored as text (e.g., with a leading apostrophe), the subtraction will fail. Select the cells, click the warning icon, and choose "Convert to Number."
- Mixing units: If you have kilometers, the same formula works, but the reimbursement rate must be per kilometer, not per mile.
Advanced: Using a Table for Auto-Expansion
Convert your data range to an Excel Table (select data, press Ctrl+T). Then, when you add a new row at the bottom, formulas in the table auto-fill. For example, if you have a calculated column for miles, the formula is automatically applied to new rows. This saves time and reduces errors. To do this: select your data, go to Insert > Table, and ensure "My table has headers" is checked. Then, in the first data cell of the miles column, enter the formula =[@End]-[@Start] (assuming columns named "Start" and "End"). Excel will fill the column.
Frequently Asked Questions
How do I calculate mileage for multiple vehicles?
Add a Vehicle column (e.g., Column E). Use =SUMIFS(D2:D100, E2:E100, "Car A") to total miles for one vehicle. For a breakdown by vehicle and month, use a PivotTable with Vehicle in Rows and Date grouped by Months.
Can Excel calculate mileage between two addresses?
No, Excel alone cannot calculate driving distance. You need a mapping service like Google Maps or a web query. You can use the =WEBSERVICE function to pull data from an API, but that requires a third-party service and is not built-in. For simple odometer-based mileage, the subtraction formula is all you need.
What if I forgot to record the starting odometer?
You can't calculate that trip's mileage accurately. If you have a previous ending odometer, you can use that as the starting point for the next trip, but the missing trip is lost. To avoid this, always record both readings at the time of the trip.
How do I use a mileage rate that changes mid-year?
Create a separate table with rate effective dates. Use a formula like =SUMPRODUCT((A2:A100>=StartDate)*(A2:A100<=EndDate)*D2:D100*Rate) but that's complex. Simpler: split your data into periods and apply the appropriate rate, or use a lookup with approximate match to find the rate for each date.
Is there a way to automatically log mileage from a GPS?
Excel doesn't have built-in GPS logging. You would need to use a third-party app that exports a CSV or Excel file, then import it. Once imported, you can use the formulas above to calculate totals.
Does the formula work in Google Sheets?
Yes, the basic arithmetic and SUMIFS formulas work identically in Google Sheets. The table auto-expansion is similar, but you may need to drag the formula down manually if you don't use a table. The main difference is the menu path: Insert > Table in Excel vs. Format > Alternate colors in Sheets, but the formulas are the same.
Related
- Excel and Access LLC: What’s the Difference?
- Excel and Access Training: What You Actually Need
- Excel Flexi Timesheet Template UK: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concat Excel Cell Range: Join Cells With Simple FormulasWith the values to join in A2:A5, enter =CONCAT(A2:A5) to combine the range into one text string; for…
- SPLIT Function Usage in ExcelThe SPLIT function does not exist in Excel; the correct way to split text in Excel is to…
- Excel Goal Seek Formula: How to Loop ItExcel does not have a worksheet function called LOOP or GOALSEEK: Goal Seek is a built-in What-If Analysis…
- Excel IFERROR: How It Works, Examples, and Common UsesExcel’s IFERROR checks a value or formula and returns that result if it is not an error; if…
- Excel COUNTIF for Numbers in Cells: Examples and FixesTo count cells that equal a number in Excel, use =COUNTIF(B2:B20,25), replacing the range and 25 with your…
- How to Set an Absolute Cell Reference in Excel FormulasTo set an absolute cell reference in Excel, add a dollar sign before both the column letter and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.