DJs usually bill overtime in half-hour blocks, not by the minute. MAX stops early finishes from creating a negative charge, and ROUNDUP turns any partial block into a full one.
5.2 hours booked against 4 included is 1.2 hours over, which bills 3 blocks.
The example
Three events. The corporate event ends inside the included time, so no overtime applies.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Case | Base | Included hrs | Actual hrs | Rate/30 min | Blocks | Total |
| 2 | Wedding | $800 | 4.0 | 5.2 | $75 | 3 | $1,025 |
| 3 | Corporate | $600 | 3.0 | 3.0 | $60 | 0 | $600 |
| 4 | Prom | $500 | 4.0 | 4.5 | $50 | 1 | $550 |
The formula
The hours past the package, doubled to count half-hours, rounded up:
How it works
Here is the logic, one piece at a time:
D2-C2is actual minus included hours.MAX(0,...)clamps early finishes at zero.*2converts hours to half-hour blocks.ROUNDUProunds partial blocks up; the total multiplies blocks by the rate.
State the block size on the contract so the rounding is never a surprise.
Try it: interactive demo
Enter the base package, included hours, actual hours and block rate.
Variations
Hourly blocks
Bill by the full hour instead.
Grace period
Ignore the first 10 minutes over.
Pitfalls & errors
Skip ROUNDUP and a 10-minute overrun bills a fraction of a block you do not charge.
Measure actual time from the contract end time to the last song, not the booking window.
Put the block rate on the quote so overtime is pre-approved.
Practice workbook
Frequently asked questions
Why double the hours?
Can I use quarter-hour blocks?
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