01 / 09The Pay Formula Read Sunday as Day 1

Date FunctionsTimesheetsFormula Auditing

WEEKDAY in Excel: Weekend Pay Rates and Return Types

The Sunday Cleaners Were Paid the Flat Rate and Fridays Went Out at Time and a Half, Because WEEKDAY Was Counting From Sunday

12 Nov 20268 min read

UsesWEEKDAYIFCOUNTIFSUMPRODUCT

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.

DayPaid atShould be
Friday1.5×1×
Saturday2×1.5×
Sunday1×2×
Bank holiday Monday1×2×

Scope. WEEKDAY with return types 1, 2 and 3, CHOOSE, IF, COUNTIF, SUMPRODUCT and TEXT work in every version of Excel. Return types 11 to 17 need Excel 2010 or later, and XLOOKUP needs 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.

ABCDEFGH
1
Shift
Date
Day
Hours
WEEKDAY(B2)
Old pay
WEEKDAY(B2,2)
Correct pay
2
SH-201
31/08/2026
Monday (bank hol.)
6
2
78
1
156
3
SH-202
04/09/2026
Friday
5
6
97.5
5
65
4
SH-203
05/09/2026
Saturday
6
7
156
6
117
5
SH-204
06/09/2026
Sunday
6
1
78
7
156
6
SH-205
07/09/2026
Monday
4
2
52
1
52
7
SH-206
09/09/2026
Wednesday
4
4
52
3
52
8
SH-207
11/09/2026
Friday
5
6
97.5
5
65
9
SH-208
12/09/2026
Saturday
6
7
156
6
117
10
SH-209
13/09/2026
Sunday
6
1
78
7
156
11
SH-210
13/09/2026
Sunday
4
1
52
7
104

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.

Try it in the grid

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 typeShiftsOld payCorrect payDifference
Bank holiday178.00156.00−78.00
Friday2195.00130.00+65.00
Saturday2312.00234.00+78.00
Sunday3208.00416.00−208.00
Weekday2104.00104.000.00
Total10897.001,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.

Try it in the grid

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 typeMondayFridaySaturdaySunday
1 or omitted2671
21567
30456
111567
163712

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.

Try it in the grid

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.

Try it in the grid

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, WEEKDAY ignores the time but COUNTIF does not. 31/08/2026 07:00 is not equal to 31/08/2026, so test INT(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 grid

06Put 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.

Try it in the grid

07Five Traps

  1. Leaving out the return type. WEEKDAY(B2) counts from Sunday. Write ,2 every time, even where the default would happen to work.
  2. 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.
  3. Testing >5 on the default type. It flags Friday and Saturday and misses Sunday, so a weekend premium lands on the wrong shifts.
  4. Testing the holiday after the weekday. A bank holiday on a weekend takes the weekend rate. Check the holiday list first.
  5. 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

  1. Reproduce it. Build the sheet with the old formula and confirm that F5 returns 78.00 for a Sunday shift.
  2. Fix the numbering. Switch to WEEKDAY(B2,2) and CHOOSE, and confirm that the Friday shifts drop to 65.00.
  3. Add the holiday. Add the COUNTIF test and confirm that the pay column totals 1,040.00.
  4. Audit by day. Use SUMPRODUCT to 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.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge