Pro workbook: Log, Fuel, Monthly, Setup, Formulas. Sample data, real formulas.
| Mileage log | |||||||||||
| One row per trip. Distance from the odometer readings; business trips are valued at your rate. | |||||||||||
| Year | 2026 | Total distance | 520 | mi | |||||||
| Rate per mi | 0.45 | what business trips are worth — your tax authority's rate or your employer's | Business | 294 | |||||||
| Unit | mi | Commute | 36 | ||||||||
| Personal | 190 | ||||||||||
| Date | From | To | Purpose | Odo start | Odo end | Distance | Claim | Business share | 57% | ||
| 1 Sep | Home | Office | Commute | 41,200 | 41,212 | 12 | Claim | 132.30 | |||
| 1 Sep | Office | Harbor Ltd | Business | 41,212 | 41,241 | 29 | 13.05 | Trips | 14 | ||
| 1 Sep | Harbor Ltd | Home | Business | 41,241 | 41,274 | 33 | 14.85 | ||||
| 3 Sep | Home | Grove Studio | Business | 41,274 | 41,318 | 44 | 19.80 | ||||
| 3 Sep | Grove Studio | Home | Business | 41,318 | 41,362 | 44 | 19.80 | ||||
| 5 Sep | Home | Supermarket | Personal | 41,362 | 41,370 | 8 | |||||
| 8 Sep | Home | Office | Commute | 41,370 | 41,382 | 12 | |||||
| 8 Sep | Office | Home | Commute | 41,382 | 41,394 | 12 | |||||
| 10 Sep | Home | Larkfield School | Business | 41,394 | 41,421 | 27 | 12.15 | ||||
| 10 Sep | Larkfield School | Home | Business | 41,421 | 41,448 | 27 | 12.15 | ||||
| 12 Sep | Home | Coast | Personal | 41,448 | 41,539 | 91 | |||||
| 12 Sep | Coast | Home | Personal | 41,539 | 41,630 | 91 | |||||
| 27 Aug | Home | Mercer & Co | Business | 41,110 | 41,155 | 45 | 20.25 | ||||
| 27 Aug | Mercer & Co | Home | Business | 41,155 | 41,200 | 45 | 20.25 | ||||
| Fuel | ||||||||||
| Fill-ups. Consumption is worked out between consecutive full tanks; cost per distance from the same pair. | ||||||||||
| Date | Odometer | Litres | Cost | Price / L | Distance | L per 100 | Cost per mi | Average L per 100 | 21.2 | |
| 20 Aug | 41,080 | 38.5 | 55.10 | 1.431 | Average cost per mi | 0.306 | ||||
| 2 Sep | 41,250 | 36.2 | 52.30 | 1.445 | 170 | 21.3 | 0.308 | Fuel this year | 165.30 | |
| 11 Sep | 41,440 | 40.1 | 57.90 | 1.444 | 190 | 21.1 | 0.305 | Fuel cost per mi (year) | 0.318 | |
| Monthly | |||||||
| Distance by purpose and the claim per month, for the year on Setup. | |||||||
| Month | Trips | Business | Commute | Personal | Total | Claim | Fuel |
| Jan 2026 | |||||||
| Feb 2026 | |||||||
| Mar 2026 | |||||||
| Apr 2026 | |||||||
| May 2026 | |||||||
| Jun 2026 | |||||||
| Jul 2026 | |||||||
| Aug 2026 | 2 | 90 | 90 | 40.50 | 55.10 | ||
| Sep 2026 | 12 | 204 | 36 | 190 | 430 | 91.80 | 110.20 |
| Oct 2026 | |||||||
| Nov 2026 | |||||||
| Dec 2026 | |||||||
| Year | 14 | 294 | 36 | 190 | 520 | 132.30 | 165.30 |
| Setup | ||
| The year, the rate per mile or km, the unit, and the purposes. | ||
| Year | 2026 | |
| Rate per unit | 0.45 | e.g. the flat rate your tax authority allows per business mile/km |
| Unit | mi | |
| Purposes | ||
| Business | ||
| Commute | ||
| Personal | ||
| Only trips whose purpose is exactly 'Business' are claimed — keep that word; rename the others freely. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Log · Distance | IF(OR(E8="",F8=""),0,F8-E8) | Odometer end minus start; zero until both are typed. A negative distance turns red. |
| Log · Claim | IF(D8="Business",G8*$B$4,0) | Distance times the rate for Business trips only. |
| Log · Business share | K4/K3 | Business distance over all distance for the year. |
| Fuel · Distance | B6-B5 | Odometer at this fill minus the previous one. |
| Fuel · L per 100 | C6/F6*100 | Litres at this fill over the distance since the last fill — that's why every fill must be a full tank. |
| Fuel · Cost per unit | D6/F6 | This fill's cost over that distance. |
| Monthly · Business | SUMIFS(Log!$G$8:$G$307,Log!$D$8:$D$307,"Business",Log!$A$8:$A$307,">="&A5,Log!$A$8:$A$307,"<="&EOMONTH(A5,0)) | Purpose plus two date bounds. |