Pro workbook: Log, Vehicles, Schedule, Setup, Formulas. Sample data, real formulas.
| Maintenance log | |||||||||||||
| One row per service, repair or cost. Days and km to the next due are calculated against today and the vehicle's latest odometer. | |||||||||||||
| As of | 14 Sep | Cost this year | 5,235.00 | ||||||||||
| Cost, all time | 5,235.00 | ||||||||||||
| Entries | 17 | ||||||||||||
| Overdue | 1 | ||||||||||||
| Date | Vehicle | Odometer | Type | Description | Cost | Next due | Next km | Days to due | Km to due | Flag | Due soon | 1 | |
| 12 Jan | Van 1 | 61,200 | Oil and filter | 140.00 | 12 Jul | 71,200 | -64 | -2,200 | Overdue | Biggest type this year | Service | ||
| 20 Jan | Van 2 | 88,400 | Service | Annual service | 420.00 | 20 Jan | 108,400 | 128 | 9,800 | ||||
| 3 Feb | Car | 24,100 | Tyres | Two front | 260.00 | ||||||||
| 14 Feb | Van 1 | 62,900 | Insurance | Annual | 1,180.00 | 14 Feb | 153 | ||||||
| 5 Mar | Van 2 | 90,100 | Brakes | Front pads and discs | 380.00 | 130,100 | 31,500 | ||||||
| 22 Mar | Car | 25,300 | Oil and filter | 95.00 | 22 Mar | 40,300 | 189 | 10,800 | |||||
| 8 Apr | Van 1 | 65,400 | Repair | Wing mirror | 85.00 | ||||||||
| 30 Apr | Van 2 | 92,300 | Oil and filter | 150.00 | 5 Oct | 102,300 | 21 | 3,700 | Due soon | ||||
| 15 May | Van 1 | 67,100 | Tyres | Four | 520.00 | 107,100 | 33,700 | ||||||
| 2 Jun | Car | 27,400 | Service | Annual service | 310.00 | 2 Jun | 42,400 | 261 | 12,900 | ||||
| 18 Jun | Van 2 | 94,600 | Repair | Sliding door lock | 130.00 | ||||||||
| 9 Jul | Van 1 | 69,800 | Oil and filter | 140.00 | 9 Jan | 79,800 | 117 | 6,400 | |||||
| 28 Jul | Van 1 | 70,900 | Service | Annual service | 460.00 | 28 Jul | 90,900 | 317 | 17,500 | ||||
| 11 Aug | Van 2 | 97,200 | Tyres | Two rear | 240.00 | ||||||||
| 25 Aug | Car | 29,500 | Battery | 145.00 | 25 Aug | 1441 | |||||||
| 3 Sep | Van 2 | 98,600 | Repair | Alternator | 410.00 | ||||||||
| 10 Sep | Van 1 | 73,400 | Brakes | Rear pads | 170.00 | 113,400 | 40,000 | ||||||
| Vehicles | ||||||||||||
| Each vehicle: latest odometer and spend from the Log, and the three dated reminders — tax, insurance, inspection. | ||||||||||||
| Vehicle | Make / model | Plate | Year | Latest km | Cost this year | Cost / km | Tax due | Insurance due | Inspection due | Next reminder | In days | Flag |
| Van 1 | Ford Transit Custom | AB12 CDE | 2021 | 73,400 | 2,695.00 | 0.221 | 30 Nov | 14 Feb | 3 Oct | Inspection | 19 | Due soon |
| Van 2 | VW Transporter | FG34 HIJ | 2019 | 98,600 | 1,730.00 | 0.170 | 31 Mar | 28 Sep | 20 Jan | Insurance | 14 | Due soon |
| Car | Toyota Corolla | KL56 MNO | 2022 | 29,500 | 810.00 | 0.150 | 31 May | 1 Dec | 11 Apr | Insurance | 78 | |
| All | 5,235.00 | |||||||||||
| Cost / km = this year's cost ÷ this year's distance (latest odometer minus the first odometer logged this year). It needs at least two log entries this year. | ||||||||||||
| Next reminder is the earliest of the three dates; move a date forward when you've renewed. |
| Service schedule | ||||||||||||||
| What each vehicle needs and how often. Last done comes from the Log; next due is by km and by date, whichever comes first. | ||||||||||||||
| Overdue | 1 | |||||||||||||
| Due soon | 1 | |||||||||||||
| Vehicle | Item | Every km | Every months | Last done | At km | Next by date | Next by km | Latest km | Days left | Km left | Status | Never done | 1 | |
| Van 1 | Oil and filter | 10,000 | 6 | 9 Jul | 69,800 | 9 Jan | 79,800 | 73,400 | 117 | 6,400 | OK | |||
| Van 1 | Service | 20,000 | 12 | 28 Jul | 70,900 | 28 Jul | 90,900 | 73,400 | 317 | 17,500 | OK | |||
| Van 1 | Brakes | 40,000 | 24 | 10 Sep | 73,400 | 10 Sep | 113,400 | 73,400 | 727 | 40,000 | OK | |||
| Van 2 | Oil and filter | 5,000 | 6 | 30 Apr | 92,300 | 30 Oct | 97,300 | 98,600 | 46 | -1,300 | Overdue | |||
| Van 2 | Service | 20,000 | 12 | 20 Jan | 88,400 | 20 Jan | 108,400 | 98,600 | 128 | 9,800 | OK | |||
| Van 2 | Brakes | 40,000 | 24 | 5 Mar | 90,100 | 5 Mar | 130,100 | 98,600 | 538 | 31,500 | OK | |||
| Car | Oil and filter | 15,000 | 6 | 22 Mar | 25,300 | 22 Sep | 40,300 | 29,500 | 8 | 10,800 | Due soon | |||
| Car | Service | 15,000 | 12 | 2 Jun | 27,400 | 2 Jun | 42,400 | 29,500 | 261 | 12,900 | OK | |||
| Car | Tyres | 40,000 | 36 | 3 Feb | 24,100 | 3 Feb | 64,100 | 29,500 | 873 | 34,600 | OK | |||
| Car | Brakes | 30,000 | 24 | 29,500 | Never done | |||||||||
| 'Item' must match the Type used on the Log — that's how 'last done' finds it. Leave 'every km' or 'every months' blank for items that only go by one of them. | ||||||||||||||
| Next by date = last done + months; next by km = km at last done + interval. Overdue or Due soon on either counts. |
| Setup | ||
| The as-of date and the types. Vehicles live on their own sheet. | ||
| As of | 2026-09-14 | Type =TODAY() to keep it live |
| Types | ||
| Oil and filter | ||
| Service | ||
| Brakes | ||
| Tyres | ||
| Battery | ||
| Repair | ||
| Inspection | ||
| Insurance | ||
| Tax | ||
| Other | ||
| Types are shared by the Log and the Schedule; keep the service items you schedule in this list. |
| 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 · Km to due | H8-SUMPRODUCT(MAX(($B$8:$B$207=B8)*$C$8:$C$207)) | Next km minus the vehicle's biggest odometer in the log. SUMPRODUCT(MAX(…)) makes MAX see the whole column without array entry — MAXIFS would be simpler but isn't in Excel 2016. |
| Log · Flag | IF(OR(AND(G8<>"",I8<0),AND(H8<>"",J8<0)),"Overdue",IF(OR(AND(G8<>"",I8<=30),AND(H8<>"",J8<=1000)),"Due soon","")) | Either the date or the km can trigger it; a blank one is ignored. |
| Vehicles · Latest km | SUMPRODUCT(MAX((Log!$B$8:$B$207=A6)*Log!$C$8:$C$207)) | The vehicle's highest odometer reading. |
| Vehicles · Cost / km | cost this year ÷ (max odometer this year − min odometer this year) | The MIN side gives every other row a huge stand-in so it doesn't win; IFERROR covers a vehicle with one entry. |
| Vehicles · Next reminder | IF(H6=MIN(H6:J6),"Tax",IF(I6=MIN(H6:J6),"Insurance","Inspection")) | Which of the three dates is soonest. |
| Schedule · Last done | SUMPRODUCT(MAX((Log!$B$8:$B$207=A6)*(Log!$D$8:$D$207=B6)*Log!$A$8:$A$207)) | Latest date in the log for this vehicle and this item; 0 (shown blank) if never. |
| Schedule · Next by date | EDATE(E6,D6) | Last done plus the interval in months. |
| Schedule · Status | IF(E6=0,"Never done",IF(OR(AND(G6<>0,J6<0),AND(H6<>0,K6<0)),"Overdue",IF(OR(AND(G6<>0,J6<=30),AND(H6<>0,K6<=1000)),"Due soon","OK"))) | Never done first; then overdue on either measure; then due soon; else OK. |