Excel WEEKDAY Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 98 of 105 · Full Excel course

The Excel WEEKDAY function returns the day of the week for a date as a number. By default Sunday is 1 and Saturday is 7, but an optional second argument lets you make Monday 1, or start the count at 0. WEEKDAY is the basis of weekend flags, week-start calculations, conditional formatting for Saturdays and Sundays, and roster and shift logic.

Table of Contents

Syntax

=WEEKDAY(serial_number, [return_type])

Arguments

Argument	Required / Optional	Meaning
serial_number	Required	The date to evaluate: a date cell, TODAY(), a DATE result or recognisable date text. Any time portion is ignored.
return_type	Optional	Sets the numbering system (see table). Default is 1.

Return type codes

return_type	Numbering	Typical use
1 or omitted	Sunday = 1 to Saturday = 7	US-style weeks
2	Monday = 1 to Sunday = 7	ISO and most business weeks; weekend test is >5
3	Monday = 0 to Sunday = 6	Zero-based offsets for week-start formulas
11 to 17	Week starts Monday (11), Tuesday (12) … Sunday (17), first day = 1	Custom week starts (Excel 2010 and later)

How WEEKDAY works

Excel stores dates as serial numbers, and the day of the week repeats every seven numbers, so WEEKDAY is essentially a MOD 7 calculation shifted to match the chosen numbering. =WEEKDAY("2026-09-04") returns 6 (Friday) with the default system and 5 with return_type 2. The result is a number, not a name; use TEXT(A2,”dddd”) for the name. Invalid dates return #VALUE! and an unsupported return_type returns #NUM!.

Step-by-step example: flag weekends in a schedule

Enter consecutive dates in column A, starting at A2.
In B2 enter =WEEKDAY(A2,2) and fill down. Monday shows 1 and Sunday shows 7.
In C2 enter =IF(B2>5,"Weekend","Weekday") and fill down.
Select A2:C40, open Conditional Formatting > New Rule > Use a formula, and enter =WEEKDAY($A2,2)>5 with a grey fill. Weekend rows are now shaded automatically.

Practical use cases

1. Monday of the current week

=A2-WEEKDAY(A2,2)+1

2. Next Monday after a date

=A2+8-WEEKDAY(A2,2)

3. Day name in any language-independent way

=CHOOSE(WEEKDAY(A2,2),"Mon","Tue","Wed","Thu","Fri","Sat","Sun")

Unlike TEXT, this returns the same labels regardless of the user’s Excel language.

4. Sales by weekday

=SUMPRODUCT((WEEKDAY($A$2:$A$500,2)=E2)*$B$2:$B$500)

With 1 to 7 in E2:E8, this builds a weekday sales profile.

5. Data validation that rejects weekend dates

=WEEKDAY(A2,2)<6

Common mistakes and tips

Default numbering surprises: with return_type omitted, Sunday is 1, so a weekend test is OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7). Return type 2 simplifies it to >5.
Expecting a name: WEEKDAY returns a number. Use TEXT(A2,”dddd”) or CHOOSE for names.
Result shown as a date: the cell has a date format; switch to General.
Text dates: “04/09/2026” depends on regional settings; real date cells avoid ambiguity.
Custom weekends in counts: for counting working days with non-standard weekends, NETWORKDAYS.INTL is more direct than WEEKDAY logic.

WEEKDAY compared with TEXT, WEEKNUM and NETWORKDAYS

TEXT(A2,”dddd”) shows the weekday name and is ideal for labels, but the result is text that cannot be compared numerically and changes with the display language. WEEKDAY gives a stable number for logic and calculation. WEEKNUM answers a different question, the week of the year, and is used for weekly reporting periods rather than day-of-week logic. NETWORKDAYS and WORKDAY handle weekend exclusion internally, so for counting or adding working days they replace hand-built WEEKDAY tests. Choose WEEKDAY when a formula must know which day a specific date falls on: rostering, conditional formatting, week-start alignment and weekday-based summaries.