Night and weekend shifts often pay a premium — a flat amount or a percentage on top of the base rate. A simple IF (or a rate lookup) applies the right differential to each shift’s hours.
The example
8 night hours at $20 +10%.
| A | B | |
|---|---|---|
| 1 | Shift | Pay |
| 2 | Night, 8h | $176 |
| 3 | Day, 8h | $160 |
The formula
The formula:
How it works
How it works:
IF(shift="Night", 1.1, 1)returns a multiplier — 1.1 for night, 1 otherwise.- Multiply hours × rate × multiplier for the differential-adjusted pay.
- For a flat differential, add it instead:
=hours * (rate + diff). - With several shift types, a lookup table of premiums beats nested IFs.
Many shift types? Replace the IF with a lookup: keep a small table of shift names and their multipliers, then =hours * rate * XLOOKUP(shift, shift_names, multipliers, 1). Adding a new shift premium becomes a table edit, not a formula rewrite.
Try it: interactive demo
Shift, hours, base rate.
Variations
Flat differential
Add per hour:
Lookup the premium
Many shifts:
Just the premium
Differential portion:
Pitfalls & errors
Percent vs flat. Decide whether the differential is a rate multiplier or a flat per-hour add — they differ.
Stacking with overtime. Check whether the premium applies before or after overtime multipliers in your rules.
Exact shift text. The IF compares text — inconsistent labels ("night" vs "Night") break it; consider a lookup.
Practice workbook
Frequently asked questions
How do I calculate shift differential pay in Excel?
How do I handle several shift types?
Is the differential a percentage or a flat rate?
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