Car Rental: Days Charged From Pickup, Return And A Grace Period

All versions

A rental day is 24 hours from pickup, and coming back late costs a whole extra day — unless you are inside the grace period. That is three ideas in one formula: subtract two date-times to get hours out, forgive the grace, then round up to whole days. Getting it right is the difference between a $49 surprise on the invoice and a customer who trusts the counter.

Quick formula: Hours out, less the grace period, divided by 24 and rounded up:

=ROUNDUP(MAX((C2-B2)*24-D2,0)/24,0)

Picked up Monday 9:00, returned Wednesday 11:00 is 50 hours; less a 1-hour grace is 49; 49 ÷ 24 = 2.04, so 3 days are charged.

Functions used (tap for the full reference guide):

ROUNDUP MAX

The example

Three contracts. The first is late enough to cost a day; the second is 45 minutes late and forgiven; the third comes back exactly on time.

	A	B	C	D	E	F
1	Rental	Pickup	Return	Grace hrs	Hours out	Days charged
2	Weekend, late	9/21/2026 9:00	9/23/2026 11:00	1	50.00	3
3	One-day, 45 min late	9/21/2026 9:00	9/22/2026 9:45	1	24.75	1
4	Three-day, on time	9/21/2026 9:00	9/24/2026 9:00	1	72.00	3

The formula

Hours out as its own column, because that is what the customer argues about:

=(C2-B2)*24 then =ROUNDUP(MAX(E2-D2,0)/24,0) // hours out, then billable days

How it works

Excel stores date-times as days, which makes the first step easy:

C2-B2 is the return minus the pickup, in days: 2.0833 days. Multiply by 24 for hours — 50.
E2-D2 forgives the grace period: 50 minus 1 is 49 billable hours.
MAX(...,0) stops an early return from producing negative hours — a car back after 30 minutes with a 1-hour grace should be 0 hours, not −0.5.
ROUNDUP(.../24,0) converts to whole days: 49 over 24 is 2.04, rounded up to 3. Two full days plus any part of a third is three days.

The 45-minute-late contract shows why the grace is inside the MAX: 24.75 minus 1 is 23.75, which rounds up to 1 day. Without the grace it would be 2.

Try it: interactive demo

Interactive

Enter pickup and return as date and time, plus the grace period in hours.

Pickup (YYYY-MM-DD HH:MM)

Return (YYYY-MM-DD HH:MM)

Grace hours

Variations

Hourly late charge instead of a full day

Some contracts bill late hours individually up to a cap. Hours past the last full day, rounded up.

=ROUNDUP(MOD(MAX(E2-D2,0),24),0)

Days as a minimum of one

A car returned inside the grace hour is still one rental day, never zero.

=MAX(1,ROUNDUP(MAX(E2-D2,0)/24,0))

Pitfalls & errors

Pickup and return must be date-times in one cell each, not a date column and a time column. If they are split, add them first: (DateCell+TimeCell).

Store the grace period in one cell the whole sheet references. When the policy changes from 59 minutes to 2 hours, you change one number.

Floating-point rounding: 72 hours can come out as 72.0000000001 and round up to 4 days. If you see it, wrap the hours in ROUND(...,4) before dividing.

Practice workbook

📊

Download the free Car Rental: Days Charged From Pickup, Return And A Grace Period practice workbook

Edit the yellow pickup, return and grace cells; hours out and days charged recalculate.

Frequently asked questions

Why multiply by 24?

Because subtracting two Excel date-times gives days, as a decimal. 2.0833 days is a true number but nobody reads it; times 24 gives 50 hours, which is what the contract talks about.

How do I show the days as a rate on the invoice?

Multiply the days-charged cell by the daily rate: =F2*DailyRate. If your contract has a weekly rate that kicks in at 5+ days, wrap it in IF(F2>=5, WeeklyRate*..., F2*DailyRate) or use the equipment-rental day-vs-week recipe.

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

Function references: ROUNDUP MAX