A rental billed for the allowed period should add charges only when it comes back late — and never credit a customer for bringing it back early. MAX with a floor of zero draws that line cleanly.
A skid steer allowed 5 days but out 8 is 3 overdue days at $95 — a $285 late charge. Returned on time, the charge is $0, not a negative.
The example
Three rentals with their allowed period, actual days out, and day rate.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Allowed | Days out | Day rate | Overdue | Extra |
| 2 | Skid steer | 5 | 8 | 95 | 3 | $285.00 |
| 3 | Compactor | 2 | 2 | 60 | 0 | $0.00 |
| 4 | Scissor lift | 7 | 9 | 140 | 2 | $280.00 |
The formula
MAX with a zero floor keeps an early return from producing a negative charge:
How it works
The zero inside MAX is the whole trick:
C2-B2is days out minus days allowed — positive when late, negative when early.MAX(0,...)floors that at zero, so an early or on-time return contributes nothing instead of a credit.*D2multiplies the overdue days by the day rate for the extra charge.- Without the MAX, a rental back two days early would show minus two days and quietly refund $190.
Some yards charge a premium day rate on overdue time to discourage it. Swap the day rate for a higher overdue rate in its own column and the formula still works.
Try it: interactive demo
Enter the allowed period, the actual days out, and the day rate.
Variations
Premium overdue rate
Bill late days at a higher rate than the contracted one.
Grace period first
Forgive a day or two before the late charge starts.
Pitfalls & errors
Leaving out the MAX(0,...) lets an early return post a negative charge that refunds part of the rental. Always floor the overdue days at zero.
Count rental days as 24-hour periods from pickup, not calendar dates. A machine out from Monday afternoon to Wednesday afternoon is two days, and the allowed column should match that convention.
Practice workbook
Frequently asked questions
Why not just use IF?
Should the allowed period bill separately?
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