Larkfield Cleaning pays £13.00 an hour, time and a half on Saturdays and double on Sundays and bank holidays. For one fortnight the pay sheet paid every weekend rate one day late, and the Sunday team noticed before payroll did.
| Day | Paid at | Should be |
|---|---|---|
| Friday | 1.5× | 1× |
| Saturday | 2× | 1.5× |
| Sunday | 1× | 2× |
| Bank holiday Monday | 1× | 2× |
Scope.
WEEKDAYwith return types 1, 2 and 3,CHOOSE,IF,COUNTIF,SUMPRODUCTandTEXTwork in every version of Excel. Return types 11 to 17 need Excel 2010 or later, andXLOOKUPneeds Excel 2021 or Microsoft 365.The rates and dates here are Larkfield's. Your own premiums will differ, but the day numbering will not.
01The Pay Formula Read Sunday as Day 1
The pay sheet lists ten shifts in A2:D11, with the date in B and the hours in D. The hourly rate, 13.00, sits in J1. The old pay formula in F2, filled to F11:
=D2*$J$1*IF(WEEKDAY(B2)=7,2,IF(WEEKDAY(B2)=6,1.5,1))
A Fortnight of Shifts and Two WEEKDAY Columns
Larkfield Cleaning's ten shifts from 31 August to 13 September 2026 in A1:H11, at £13.00 an hour. E is WEEKDAY with no return type and F the old pay built on it. G is WEEKDAY with return type 2, and H the correct pay: time and a half on Saturday, double on Sunday and on the bank holiday.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Whoever wrote it counted the week from Monday, so Saturday was 6 and Sunday 7. WEEKDAY with no second argument counts from Sunday. It returns 1 for Sunday, 6 for Friday and 7 for Saturday.
So the formula paid Friday at time and a half, Saturday at double and Sunday at the flat rate. Column C showed the right day names all along, which is why nobody looked twice at the rates.
Scenario: Enter 06/09/2026 in B5 and =WEEKDAY(B5) in E5. Check that it returns 1, even though the date is a Sunday and the pay formula expects a 7.
02Every Weekend Rate Moved One Day Along
Each weekend day took the rate meant for the day after it. The overpayments and the shortfalls netted off, so the fortnight's total looked only slightly low.
| Day type | Shifts | Old pay | Correct pay | Difference |
|---|---|---|---|---|
| Bank holiday | 1 | 78.00 | 156.00 | −78.00 |
| Friday | 2 | 195.00 | 130.00 | +65.00 |
| Saturday | 2 | 312.00 | 234.00 | +78.00 |
| Sunday | 3 | 208.00 | 416.00 | −208.00 |
| Weekday | 2 | 104.00 | 104.00 | 0.00 |
| Total | 10 | 897.00 | 1,040.00 | −143.00 |
The sheet was £143.00 under in total. The real damage was £286.00 short on four shifts and £143.00 over on four others, each one owed to a different person.
A reasonableness check on the total cannot see this. Hours times rate times "about 1.2" lands near both columns. Only a check by day type finds it.
Scenario: Enter =SUM(F2:F11) and =SUM(H2:H11) under the two pay columns. Check that they return 897.00 and 1,040.00, then subtract one from the other and confirm the gap is 143.00.
03Give WEEKDAY a Return Type Every Time
WEEKDAY takes an optional second argument that says which day is 1. Leaving it out is a choice, and it chooses Sunday. Write it every time, so the reader does not have to remember.
| Return type | Monday | Friday | Saturday | Sunday |
|---|---|---|---|---|
| 1 or omitted | 2 | 6 | 7 | 1 |
| 2 | 1 | 5 | 6 | 7 |
| 3 | 0 | 4 | 5 | 6 |
| 11 | 1 | 5 | 6 | 7 |
| 16 | 3 | 7 | 1 | 2 |
Type 2 is the one to standardise on in a workbook that counts Monday as the first day. In G2, filled to G11:
=WEEKDAY(B2,2)
With type 2, the weekend test is =WEEKDAY(B2,2)>5. It is TRUE on Saturday and Sunday and nowhere else. The same >5 on the default type flags Friday and Saturday and lets Sunday through.
Scenario: Enter =WEEKDAY(B2,2) in G2 and fill it to G11. Check that G5, G10 and G11 return 7 for the three Sunday shifts, and G3 returns 5 for Friday.
04One Multiplier per Day With CHOOSE
Nested IFs hide the policy inside the logic. CHOOSE lists the seven multipliers in day order, so the formula reads like the rule it applies:
=CHOOSE(WEEKDAY(B2,2),1,1,1,1,1,1.5,2)
The slots run Monday to Sunday: five 1s, then 1.5 for Saturday and 2 for Sunday. Anyone checking it can count along the list. Wrapped into the pay for SH-204, the Sunday shift in row 5:
=D5*$J$1*CHOOSE(WEEKDAY(B5,2),1,1,1,1,1,1.5,2)
It returns 156.00, not the 78.00 the old formula paid. If the rates change often, put them in a table instead.
With the numbers 1 to 7 in L2:L8 and the multipliers beside them in M2:M8:
=XLOOKUP(WEEKDAY(B2,2),$L$2:$L$8,$M$2:$M$8)
Now a new Saturday premium is one cell for HR to edit, not a formula to rewrite. Label L with day names in a column of its own, so nobody has to guess what 6 means.
Scenario: Enter =D5*$J$1*CHOOSE(WEEKDAY(B5,2),1,1,1,1,1,1.5,2) in H5 and check that it returns 156.00. Then point it at row 4, the Saturday shift, and check that it returns 117.00.
05Test the Bank Holiday First
Monday 31 August 2026 was the summer bank holiday, paid at double. No day-of-week rule can know that: WEEKDAY says 1, a Monday, and CHOOSE pays it at 1×.
Keep the holidays in a list, here J3:J4, and test it before the day of the week. In H2, filled to H11:
=D2*$J$1*IF(COUNTIF($J$3:$J$4,B2),2,CHOOSE(WEEKDAY(B2,2),1,1,1,1,1,1.5,2))
COUNTIF returns 1 for SH-201, so IF takes the 2 and H2 returns 156.00. Every other date returns 0, and the CHOOSE decides.
The order matters. A holiday that falls on a Saturday must still pay the holiday rate, so the holiday test goes first and the weekday rule only runs when it fails.
If column B holds a start time as well as a date,
WEEKDAYignores the time butCOUNTIFdoes not. 31/08/2026 07:00 is not equal to 31/08/2026, so testINT(B2)against the list instead.Type the holiday dates as dates, or build them with
=DATE(2026,8,31), so they compare as numbers.
Scenario: Put 31/08/2026 and 25/12/2026 in J3:J4 and the full formula in H2, then fill it to H11. Check that H2 returns 156.00 and that the column totals 1,040.00.
Try it in the grid06Put the Day Name Beside the Number
A weekday number means nothing at a glance. A day name beside it makes a wrong number obvious. Next to the old column:
=TEXT(B2,"dddd")
Row 5 now reads Sunday beside a 1, and the old formula's assumption falls apart on sight. Delete the helper once the rates are fixed, or keep it as a permanent audit column.
A second check counts hours by day, the way the payslips will be queried. Sunday hours under type 2:
=SUMPRODUCT((WEEKDAY(B2:B11,2)=7)*D2:D11)
It returns 16, the three Sunday shifts. The old weekend test, =SUMPRODUCT((WEEKDAY(B2:B11)>5)*D2:D11), returns 22: ten Friday hours and twelve Saturday hours, and no Sunday at all.
Scenario: Enter =SUMPRODUCT((WEEKDAY(B2:B11,2)=7)*D2:D11) in J6 and check that it returns 16. Then enter the old weekend version in J7 and check that it returns 22.
07Five Traps
- Leaving out the return type.
WEEKDAY(B2)counts from Sunday. Write,2every time, even where the default would happen to work. - Mixing types across one workbook. A pay column on type 2 beside a conditional formatting rule on type 1 shades the wrong days. Use one type everywhere.
- Testing
>5on the default type. It flags Friday and Saturday and misses Sunday, so a weekend premium lands on the wrong shifts. - Testing the holiday after the weekday. A bank holiday on a weekend takes the weekend rate. Check the holiday list first.
- Trusting a typed day-name column. A name typed by hand can disagree with the date beside it. Build it with
TEXT, never type it.
08Four Exercises
- Reproduce it. Build the sheet with the old formula and confirm that F5 returns 78.00 for a Sunday shift.
- Fix the numbering. Switch to
WEEKDAY(B2,2)andCHOOSE, and confirm that the Friday shifts drop to 65.00. - Add the holiday. Add the
COUNTIFtest and confirm that the pay column totals 1,040.00. - Audit by day. Use
SUMPRODUCTto count Saturday hours under both return types and explain the difference.
What to Take Away
Every date and every hour on Larkfield's sheet was right. WEEKDAY counted from Sunday, and the formula assumed Monday, so each weekend rate landed one day late.
Write the return type every time, list the multipliers in day order with CHOOSE or a rate table, test bank holidays first, and check pay by day type rather than by the total.