How to Calculate Shift Differential Pay in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
Shift differential pay is extra pay for evening, night or weekend hours. In Excel, count the hours inside the differential window with =MAX(0,MIN(end,window_end)-MAX(start,window_start))*24, multiply them by the extra rate, and add the result to base pay. Use IF with WEEKDAY for weekend rates, and add 1 to end times that pass midnight.
Key takeaways
- Excel stores times as fractions of a day, so multiply time differences by 24 to get hours.
- =MAX(0,MIN(C2,$H$2)-MAX(B2,$H$1))*24 counts only the hours that fall inside the differential window.
- For shifts that pass midnight, add 1 to the end time: =C2+(C2<B2).
- =IF(WEEKDAY(A2,2)>5,...) applies a weekend rate only on Saturdays and Sundays.
- Keep rates and window times in input cells so a policy change means editing one cell.
On this page+
Shift differential pay in Excel comes down to one question: how many hours of each shift fall inside the extra-pay window? A shift differential is extra pay for less popular hours, such as evenings, nights or weekends. It's usually a flat amount per hour or a percentage of the base rate. With MIN and MAX you count the qualifying hours, with IF you apply weekend rules, and the pay follows. This guide is for payroll staff, shift supervisors and anyone checking their own pay.
The rates below are examples to show the method. Real differential rules depend on your employer's policy, any contract or agreement, and local employment law.
How shift differential pay works#
A differential policy has three parts. Put each one in its own input cell, so the formulas never contain typed rates:
| Input | Example value | Cell |
|---|---|---|
| Base hourly rate | 20.00 | H4 |
| Evening window start | 18:00 | H1 |
| Evening window end | 24:00 (midnight) | H2 |
| Evening differential per hour | 2.00 | H3 |
| Weekend differential per hour | 1.50 | H5 |
Type midnight as 24:00. Excel stores it as 1, one full day, which is exactly what the formulas need. Formatted as h:mm it shows 0:00; use the custom format [h]:mm to see 24:00.
Step 1: Calculate total hours worked#
Excel stores times as fractions of a day: 12:00 is 0.5 and 18:00 is 0.75. So subtracting two times gives a fraction of a day, and multiplying by 24 turns it into hours:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Start | End | Hours |
| 2 | Fri 6 Nov 2026 | 15:00 | 23:00 | =(C2-B2)*24 |
The result is 8 hours. Format D2 as a number, not a time. The lesson on how Excel stores dates explains why dates and times are numbers underneath. For full timesheets with breaks, see the timesheet guide.
Step 2: Count the hours inside the differential window#
The qualifying hours are the overlap between the shift and the window. The overlap starts at the later of the two start times and ends at the earlier of the two end times:
=MAX(0,MIN(C2,$H$2)-MAX(B2,$H$1))*24For the 15:00 to 23:00 shift:
MIN(C2,$H$2)is the earlier end: 23:00 (the shift ends before midnight).MAX(B2,$H$1)is the later start: 18:00 (the window starts after the shift).- 23:00 − 18:00 = 5 hours.
*24turns the fraction into 5. MAX(0, ...)returns 0 for a day shift that never reaches 18:00, instead of a negative number.
The $ signs keep the window cells fixed when you fill the formula down. The MIN function guide and the MAX function guide explain both functions.
Step 3: Handle overnight shifts#
A night shift such as 20:00 to 04:00 ends "earlier" than it starts, so C2-B2 is negative. Add one day to any end time that's smaller than its start. In a helper column E2:
=C2+(C2<B2)(C2<B2) is TRUE (1) for overnight shifts and FALSE (0) otherwise. Now total hours are =(E2-B2)*24, which gives 8 for 20:00 to 04:00.
Night windows often cross midnight too, for example 22:00 to 06:00. Write the window end as 1+TIME(6,0,0), meaning 06:00 the next day, and add a second part for shifts that start in the early morning:
=(MAX(0,MIN(E2,1+TIME(6,0,0))-MAX(B2,TIME(22,0,0)))+MAX(0,MIN(E2,TIME(6,0,0))-B2))*24For 20:00 to 04:00, the first part counts 22:00 to 04:00 and the second part is 0, so the result is 6 night hours. For a 02:00 to 10:00 shift, the second part counts 02:00 to 06:00: 4 hours. The guide to overtime hours uses the same midnight fix.
Step 4: Add weekend differentials with IF#
Weekend rules depend on the date, not the time. WEEKDAY with return type 2 numbers Monday as 1 through Sunday as 7, so anything above 5 is a weekend:
=IF(WEEKDAY(A2,2)>5,D2*$H$5,0)For an 8-hour shift on Saturday 7 November 2026, this returns 8 × 1.50 = 12.00. On Friday 6 November it returns 0. The IF function guide covers the logic test. To handle public holidays too, list them in a range and add COUNTIF(holidays,A2)>0 inside an OR.
Step 5: Calculate total pay#
Add base pay and every differential. With evening hours in F2 and the weekend amount in G2:
=D2*$H$4+F2*$H$3+G2| Shift | Hours | Evening hours | Weekend extra | Pay |
|---|---|---|---|---|
| Fri 15:00–23:00 | 8 | 5 | 0.00 | 170.00 |
| Sat 09:00–17:00 | 8 | 0 | 12.00 | 172.00 |
| Mon 12:00–20:00 | 8 | 2 | 0.00 | 164.00 |
Friday: 8 × 20 + 5 × 2 = 170. Saturday: 8 × 20 + 12 = 172. Monday: 8 × 20 + 2 × 2 = 164.
If your differential is a percentage instead, such as 10% extra for evening hours, use =F2*$H$4*10% for the extra amount. That's 5 × 20 × 10% = 10.00 for the Friday shift. Check whether your policy lets differentials stack, and whether they count towards overtime rates; that's a policy question, not a formula one. The bonus calculation guide shows similar tiered-pay logic.
Common mistakes#
- Forgetting
*24. Without it, 5 hours shows as 0.21 (5 ÷ 24). Multiply by 24 and format as a number. - Typing times as text. "6pm" typed with a space or an apostrophe stays text. Enter times like 18:00 and check they align to the right.
- Negative hours on night shifts. Add 1 to end times that pass midnight before you subtract.
- Rates typed into formulas. Put them in input cells. When the policy changes, you edit one cell, not every row.
- Wrong WEEKDAY type. Without the 2, WEEKDAY counts Sunday as 1, and
>5would catch Friday and Saturday instead of Saturday and Sunday.
Planning who works which shift is a separate task; see the shift schedule guide.
Practise it#
Shift differentials rest on two skills: IF logic for the pay rules, and knowing that dates and times are numbers. Practise the IF function in a live spreadsheet, then practise how Excel stores dates with DATE, YEAR, MONTH and DAY. Each lesson checks your formula instantly. Then try the exercise on this page to count the evening hours in a shift.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The evening differential applies from hour 18 to hour 24 (midnight). Hours are written as numbers, so 15 means 3 pm. In E2, count how many hours of the 15 to 23 shift fall in that window, using MIN and MAX.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Evening hours | ||||||||
| 2 | |||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 |
Tip: The overlap ends at the earlier of the shift end and 24 (MIN), and starts at the later of the shift start and 18 (MAX). MAX(0,...) stops a negative result. The hours are already numbers, so no *24 is needed here.
Practise this in a lesson
Frequently asked questions
How do you calculate shift differential pay?
Count the hours worked inside the differential period, such as 18:00 to midnight, and multiply them by the extra rate. Add that to the regular pay for all hours. For a percentage differential, multiply the base rate by the percentage and by the differential hours.
How do I calculate hours between two times in Excel?
Subtract the start time from the end time and multiply by 24: =(C2-B2)*24. If the shift passes midnight, use =(C2+(C2<B2)-B2)*24 so the end time counts as the next day.
How do I count only the hours after 6 pm in Excel?
Use =MAX(0,MIN(C2,1)-MAX(B2,TIME(18,0,0)))*24, where B2 is the start time and C2 the end time. MIN and MAX keep only the part of the shift between 18:00 and midnight, and MAX(0,...) returns 0 if there is no overlap.
How do I add a weekend differential in Excel?
Test the day with WEEKDAY: =IF(WEEKDAY(A2,2)>5,D2*weekend_rate,0). With return type 2, Monday is 1 and Sunday is 7, so values above 5 are Saturday and Sunday.
Is shift differential pay required by law?
It depends on the country, the region, the industry and any contract or agreement that applies. Check your employer's policy and local employment rules; Excel only does the arithmetic.