When a shipper holds a truck past the free time (usually 2 hours), detention is owed for the extra hours at an hourly rate — often capped per day. It compensates for lost driving time.
The example
5 hrs waited, 2 free, $60/hr.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 5 − 2 | 3 hrs |
| 3 | × $60 | → $180 |
The formula
The formula:
How it works
How it works:
- Subtract the free time from hours waited; MAX(…, 0) floors it at zero.
- Multiply billable hours by the detention hourly rate.
- Apply a daily cap with
MIN(pay, daily_cap)if the contract sets one. - Accurate arrival/departure timestamps are what make a detention claim stick.
Detention is only as good as your timestamps. The pay math is trivial — billable hours past free time, times the rate — but collecting it depends on documented check-in and check-out times the shipper can’t dispute. Round to the contract’s increment, apply any daily cap, and keep the arrival/departure record (ELD, gate logs) so the claim holds up. The formula is easy; the proof is the work.
Try it: interactive demo
Hours waited, free hours, hourly rate.
Variations
With a daily cap
Limit the pay:
Billable hours
Past free time:
Hours from timestamps
Departure − arrival:
Pitfalls & errors
Floor at zero. Within free time, detention is $0 — MAX.
Daily cap. Apply MIN if the contract caps detention per day.
Document times. Detention claims need solid arrival/departure records.
Practice workbook
Frequently asked questions
How do I calculate detention pay in Excel?
How do I apply a daily cap?
How do I get the hours from timestamps?
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