A renter who is 12 minutes late and one who is 55 minutes late both used a fraction of an extra hour, and most agencies bill both the same way: round up to the next hour block. This recipe uses CEILING to round hours-late up to a whole hour before applying the hourly late fee, then caps the whole charge at the daily rate so a customer who is very late never pays more than if they had just extended the rental a full day.
1.2 hours late rounds up to 2 hours × $15 = $30. 5 hours late would be $75, but the $60 daily-rate cap applies instead.
The example
Four returns with different amounts of lateness. The cap keeps the fourth customer's charge from exceeding a full extra day.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rental | Hours late | Hour blocks billed | Fee |
| 2 | R-1042 | 1.2 | 2 | $30 |
| 3 | R-1043 | 0.3 | 1 | $15 |
| 4 | R-1044 | 4.0 | 4 | $60 |
| 5 | R-1045 | 5.0 | 5 | $60 |
The formula
Round up the hour blocks first, then apply the rate and the cap:
How it works
Three steps nested into one formula:
CEILING(B2,1)rounds hours late up to the next whole number. 1.2 becomes 2; 0.3 becomes 1 — any fraction of an hour counts as a full hour block.*15applies the hourly late-fee rate to the rounded hour count.MIN(...,60)compares that charge against the $60 daily rate and keeps whichever is smaller, so 5 hours late ($75 uncapped) bills at the $60 cap instead.
Change the 1 in CEILING to a smaller fraction (like 0.25) if your policy bills in 15-minute blocks instead of whole hours.
Try it: interactive demo
Enter hours late, the hourly late rate and the daily rate cap.
Variations
Bill in 15-minute blocks instead of hours
Work in minutes and round to the nearest quarter-hour block for agencies that bill more granularly than whole hours.
Waive the fee inside a grace window
Most agencies do not charge for the first 15–30 minutes. Subtract the grace period before rounding so a return 10 minutes late costs nothing.
Pitfalls & errors
CEILING with a negative number and a positive significance returns an error in some Excel builds. Wrap the hours-late input in MAX(0,...) so an early return (a negative 'late' value) never breaks the formula.
CEILING(B2,1) rounds a decimal fraction like 2.0000001 (a floating-point artifact from an earlier subtraction) up to 3. Round the hours-late input first with ROUND(x,4) if it comes from subtracting two datetime values.
Show the customer the hour-block count, not just the fee. Seeing '2 hour blocks' explains why 61 minutes late costs the same as 119 minutes late far better than the dollar figure alone.
Practice workbook
Frequently asked questions
Why not just multiply hours late directly instead of rounding up?
Does the cap ever make a short lateness cost more than expected?
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