WEEKDAY function

WEEKDAY syntax

Argument	Required	What it means
serial_number	Yes	The date: a cell holding a date, or a formula that returns one.
return_type	No	Which day counts as 1. Left out, or 1: Sunday to Saturday. 2: Monday to Sunday. 3: Monday as 0 to Sunday as 6. Codes 11 to 17 start the week on each day from Monday to Sunday.

Examples

The default: Sunday is 1

15 January 2026 is a Thursday. With no return_type the week starts on Sunday, so Thursday is day 5.

B2fx=WEEKDAY(A2)

Row	A	B
1	Date	Day number
2	15 Jan 2026	5

=WEEKDAY(A2) → 5Copy

Monday as day 1

Return type 2 starts the week on Monday, so the same Thursday is day 4.

B2fx=WEEKDAY(A2,2)

Row	A	B
1	Date	Day number
2	15 Jan 2026	4

=WEEKDAY(A2,2) → 4Copy

Flag the weekends

With return type 2, Saturday is 6 and Sunday 7, so anything above 5 is a weekend. 17 January 2026 is a Saturday.

B2fx=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

Row	A	B
1	Date	Type
2	17 Jan 2026	Weekend

=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") → WeekendCopy

The Monday of a date's week

Return type 3 counts Monday as 0, so subtracting it from a date always lands on the Monday of that week.

B2fx=A2-WEEKDAY(A2,3)

Row	A	B
1	Date	Week starts
2	15 Jan 2026	12 Jan 2026

=A2-WEEKDAY(A2,3) → 12 Jan 2026Copy

Practise it

New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.

Your task: In C2, work out the pay for the shift: the hours in B2 times the rate in E2, at time and a half (1.5 times) when the date in A2 is a Saturday or a Sunday.

Graded live

C2fx

Row	A	B	C	D	E
1	Shift date	Hours	Pay		Hourly rate
2	14 Mar 2026	8			$20.00

Write the formula in the bar above, then press Check or Enter.

Hint

WEEKDAY(A2,2) is 6 on a Saturday and 7 on a Sunday. Multiply B2*E2 by IF(WEEKDAY(A2,2)>5,1.5,1).

Solution

=B2*E2*IF(WEEKDAY(A2,2)>5,1.5,1) Any formula that returns the same result passes.

Check

Common mistakes

1. Forgetting the return type. Without one, Sunday is 1 and Saturday 7, so a test such as WEEKDAY(A2)>5 picks Friday and Saturday. Use WEEKDAY(A2,2)>5 for the weekend.
2. Expecting a name. WEEKDAY returns a number. For Monday or Mon, use TEXT(A2,"dddd") or TEXT(A2,"ddd").
3. A return type that doesn't exist. Only 1, 2, 3 and 11 to 17 are allowed; any other gives #NUM!.

Return types

return_type	The week runs
1, or left out	Sunday (1) to Saturday (7)
2	Monday (1) to Sunday (7)
3	Monday (0) to Sunday (6)
11	Monday (1) to Sunday (7)
12 to 17	Day 1 is Tuesday (12), Wednesday (13) and so on to Sunday (17); day 7 is the day before it

Types 2 and 11 give the same numbers. For a weekend test, return type 2 is the one to remember: Saturday is 6 and Sunday 7.