Skip to content

Excel function · Date & time

Excel WEEKDAY function

WEEKDAY returns which day of the week a date is, as a number from 1 to 7. For weeks that start on Monday, as in most of Europe, use return type 2: Monday is 1 and Sunday is 7.

In Turkish Excel: HAFTANINGÜNÜ

What it does

Without the return_type argument, Excel follows the US habit: Sunday is 1 and Saturday is 7. Where the week starts on Monday, =WEEKDAY(A2,2) is more natural: Monday 1, Friday 5, Saturday 6, Sunday 7. The weekend test then comes down to one comparison: WEEKDAY(A2,2)>5.

The result is a number, not a day name; to turn it into a name, use CHOOSE or SWITCH. Type 3 gives Monday a 0, which makes the start of the week easy to find: =A2-WEEKDAY(A2,3) is the Monday of that week.

For working-day calculations, rather than testing days one by one, NETWORKDAYS and WORKDAY skip weekends by themselves. To find the week number of the year, use WEEKNUM.

Syntax

English Excel=WEEKDAY(serial_number, [return_type])
Turkish Excel=HAFTANINGÜNÜ(tarih; [tür])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
serial_numberRequiredThe date, or the cell holding the date, whose weekday you want.
return_typeOptionalThe numbering scheme. 1 or omitted: Sunday 1 … Saturday 7. 2: Monday 1 … Sunday 7 (for Monday-first weeks). 3: Monday 0 … Sunday 6. Values from 11 to 17 start the week on another day.

Examples

1Return type 1 versus return type 2

The same dates are numbered with two schemes. Column B has no return_type (Sunday is 1); column C uses type 2 (Monday is 1).

B2fx=WEEKDAY(A2)
Return type 1 versus return type 2: example table
ABC
1DateType 1 (Sunday = 1)Type 2 (Monday = 1)
22-Nov-202621
36-Nov-202665
47-Nov-202676
58-Nov-202617
  • B2 (filled down to B5)=WEEKDAY(A2)
  • C2 (filled down to C5)=WEEKDAY(A2,2)

Monday 2 November is 2 with type 1 and 1 with type 2. Sunday 8 November is the first day of the week with type 1 (1) and the last with type 2 (7).

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Move a weekend due date to Monday

Column C finds the weekday of the due date with type 2. Column D adds 2 days to a Saturday (6) and 1 day to a Sunday (7); weekday due dates stay as they are.

C2fx=WEEKDAY(B2,2)
Move a weekend due date to Monday: example table
ABCD
1InvoiceDue DateWeekdayPayment Day
2F-21016-Nov-202656-Nov-2026
3F-21027-Nov-202669-Nov-2026
4F-21038-Nov-202679-Nov-2026
5F-210410-Nov-2026210-Nov-2026
  • C2 (filled down to C5)=WEEKDAY(B2,2)
  • D2 (filled down to D5)=B2+IF(C2=6,2,IF(C2=7,1,0))

The due date on Saturday 7 November moves to 9-Nov-2026; the one on Sunday 8 November lands on the same day: 9-Nov-2026. The Friday and Tuesday due dates do not change.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

Off by one day

Cause: return_type was left out, so Monday is 2 and Friday 6, and the >5 test treats Friday as the weekend.

Fix: For a Monday-first week, use type 2: WEEKDAY(A2,2).

#NUM!

Cause: return_type is not valid, e.g. 4.

Fix: Use 1, 2, 3 or a value from 11 to 17.

#VALUE!

Cause: The date is stored as text and cannot be read.

Fix: Convert it to a real date.

Good to know

  • To filter weekend rows or shade them with conditional formatting, use WEEKDAY(A2,2)>5 as the condition.
  • The Monday of the week a date belongs to: =A2-WEEKDAY(A2,2)+1.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a Payment Day column to the payment list that moves due dates falling on a weekend to the following Monday

Convert to table

Bring your payment list into the tool and type this. Nexell adds the Payment Day column with real Excel formulas and the preview shows which due dates moved; in the downloaded file, the payment day changes when the due date does.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I check whether a date falls on a weekend in Excel?

Use =WEEKDAY(A2,2)>5; it returns TRUE for Saturday and Sunday. To see it as text, use =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday").

What is the difference between return type 1 and 2 in WEEKDAY?

With type 1 the week starts on Sunday (Sunday 1, Monday 2); with type 2 it starts on Monday (Monday 1, Sunday 7). Type 2 matches a Monday-to-Friday working week.

Related functions

All date & time functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free