A 2 a.m. lockout is not a 2 p.m. job. Keep one price sheet and let IF apply the 1.5× labor multiplier whenever the call is flagged after-hours.
A $45 trip and 1 hour at $95 bills $140 by day — and $187.50 at 2 a.m., because the multiplier hits labor only.
The example
Three calls, each with a trip fee, hours, rate, and an after-hours flag.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Job | Trip | Hours | Rate | After hrs? | Total |
| 2 | Lockout 2am | $45 | 1 | $95 | Yes | $187.50 |
| 3 | Rekey afternoon | $45 | 2 | $95 | No | $235.00 |
| 4 | Safe opening Sat | $45 | 2.5 | $95 | Yes | $401.25 |
The formula
The multiplier applies to labor, not the trip fee:
How it works
One flag column controls the premium:
B2is the trip fee — same day or night in this setup.C2*D2is straight labor: hours times rate.IF(E2="Yes",1.5,1)multiplies labor by 1.5 only when the after-hours flag is set.
If your trip fee also doubles at night, wrap it too: =B2*IF(E2="Yes",2,1)+…
Try it: interactive demo
Enter the trip fee, hours, rate, and whether it is after hours.
Variations
Different night and holiday tiers
Use a nested IF or lookup for 1.5× nights and 2× holidays.
Pitfalls & errors
Type the flag exactly as the formula expects ("Yes") — a stray space bills a 2 a.m. call at the day rate. Data validation with a Yes/No list prevents it.
Publish the multiplier on your website — customers accept a stated 1.5× far better than a mystery number at 2 a.m.
Practice workbook
Frequently asked questions
Should the trip fee get the multiplier too?
What counts as after hours?
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