Flag when service is due. Compare current mileage to the next service point (last service plus the interval) and show miles remaining — or use MOD to find how far past an interval you are.
The example
Oil every 5,000; last at 30k, now 34k.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Next due | 35000 |
| 3 | 34,000 now | → 1,000 left |
The formula
The formula:
How it works
How it works:
- Next service = last_service_miles + interval (e.g. 30,000 + 5,000 = 35,000).
- Subtract current miles for miles remaining; negative means overdue.
- Flag status with
IF(remaining <= 0, "Overdue", IF(remaining < 500, "Soon", "OK")). - For a recurring interval from zero,
MOD(current_miles, interval)shows miles into the cycle.
Conditional formatting turns it into a dashboard. A fleet sheet with miles-remaining per vehicle, shaded red when overdue and amber when within 500 miles, surfaces what needs servicing at a glance. The same IF logic drives both the status text and the color rule — one source of truth for the maintenance schedule.
Try it: interactive demo
Last service, interval, current miles.
Variations
Status flag
Due soon/overdue:
Into the cycle
MOD from zero:
Next service mileage
The trigger:
Pitfalls & errors
Negative = overdue. A negative “remaining” means service is past due.
Time or miles. Some services are date-based — track both where needed.
Update last service. Keep the last-service mileage current after each visit.
Practice workbook
Frequently asked questions
How do I track maintenance intervals in Excel?
How do I flag service status?
How do I find how far into an interval I am?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class