Excel WEEKDAY Function: Complete Guide (2026)
Home›Functions›Date & Time›WEEKDAY
ExcelFunction Reference⏱ 6 min read

Excel WEEKDAY Function: Complete Guide

WEEKDAY returns the day of the week as a number — but which number depends on which counting system you choose. The second argument (return_type) is the whole game: US style (Sunday=1), ISO style (Monday=1), or zero-based (Monday=0). Get this right and WEEKDAY becomes powerful.

Syntax

WEEKDAY(serial_number, [return_type])
ArgumentTypeDescription
serial_number RequiredDateThe date to check. Any Excel-recognized date value.
return_type OptionalNumber 1-17Which numbering system to use. Defaults to 1 (US style: Sunday=1). See the table below.

The three main return_types

return_typeMonTueWedThuFriSatSun
1 (default, US)2345671
2 (ISO / EU)1234567
3 (zero-based)0123456

Return types 11-17 exist for various single-day-start conventions (11 = Monday=1, 12 = Tuesday=1, etc.) but are rarely needed. Stick with 1, 2, or 3 for almost all use cases.

Basic examples

Default (US style)

=WEEKDAY("2026-09-06")
Sep 6, 2026 is a Sunday → Result: 1

ISO style (Monday=1)

=WEEKDAY("2026-09-06", 2)
Sunday → Result: 7

Zero-based (Monday=0)

=WEEKDAY("2026-09-06", 3)
Sunday → Result: 6

Common uses

1. Is it a weekend?

Check for Saturday or Sunday

=WEEKDAY(A1, 2) > 5
TRUE for weekends (Sat=6, Sun=7 in return_type=2), FALSE for weekdays.

2. Is it a specific day?

Is A1 a Friday?

=WEEKDAY(A1, 2) = 5

3. Next Monday

Jump to Monday

=A1 + (8 - WEEKDAY(A1, 2)) MOD 7
Days until next Monday, added to A1. Sunday → 1 day forward. Monday → 7 days forward (next Monday).

4. This week's Monday

Beginning of the current week

=A1 - WEEKDAY(A1, 3)
Return_type=3 makes Monday=0, so subtracting gives Monday.

5. Conditional formatting: weekend rows

Select your date column → Conditional Formatting → New Rule → Formula:

=WEEKDAY($A1, 2) > 5

Highlight fill = light gray. All weekend rows get shaded.

6. Count weekdays in a range

How many weekdays?

=SUMPRODUCT(--(WEEKDAY(A2:A100, 2) <= 5))
Count of weekday entries in the range.

7. Fill down day names alongside dates

Show the day name

=TEXT(A1, "dddd")
Returns "Sunday", "Monday", etc. Use "ddd" for "Sun", "Mon".

8. Business day skip for scheduling

If weekend, move to Monday

=A1 + CHOOSE(WEEKDAY(A1, 2), 0, 0, 0, 0, 0, 2, 1)
Adds 0 for weekdays (positions 1-5), 2 for Saturday (position 6), 1 for Sunday (position 7).

Day names — use TEXT, not WEEKDAY

WEEKDAY always returns a number. For day names:

FormulaResult
=WEEKDAY(A1)1-7 (a number)
=TEXT(A1, "d")Day of month (1-31)
=TEXT(A1, "ddd")"Sun", "Mon", "Tue"
=TEXT(A1, "dddd")"Sunday", "Monday", "Tuesday"

Common pitfalls

Default is Sunday=1

WEEKDAY without a second argument uses return_type=1 (US Sunday-start). Most non-US users expect Monday=1, which requires WEEKDAY(A1, 2). Always specify return_type explicitly to avoid confusion.

Mixing return_types

If some formulas use return_type=1 and others use 2, comparisons break. WEEKDAY(A1) = WEEKDAY(B1, 2) can produce wrong results. Standardize on one return_type across a workbook.

Text dates fail differently

Like other date functions, WEEKDAY needs a real date. Text like "2026-09-06" usually auto-converts, but odd formats may fail. Wrap in DATEVALUE for text-heavy imports.

Use return_type=3 for cleaner math

Zero-based (Monday=0) makes arithmetic simpler. To find the Monday of the current week: =A1 - WEEKDAY(A1, 3). Just subtract the WEEKDAY result.

WEEKDAY in Google Sheets

Google Sheets has an identical WEEKDAY function with the same return_types. Sheets also has WEEKDAY.INTL for one-off cases, matching Excel's behavior.

Related functions

Related

YEAR / MONTH / DAY

Extract other date components.

Related

NETWORKDAYS

Count workdays (uses WEEKDAY internally).

Related

WORKDAY

Business-day math.

Related

TODAY

Test whether today is a specific day.

Excel Wizard

Weekday logic, US or ISO

WEEKDAY's three return_types trip up almost everyone. Excel Wizard asks what convention you want (Sunday-start, Monday-start, zero-based) and writes the formula with the correct return_type — no more mental gymnastics over which number is which day.

Install Excel Wizard →

Frequently asked questions

What does WEEKDAY do in Excel?

WEEKDAY returns the day of the week as a number for a given date. Syntax: =WEEKDAY(serial_number, [return_type]). Return_type controls the numbering system.

How do I make Monday = 1 in WEEKDAY?

Pass 2 as return_type: =WEEKDAY(A1, 2). Monday=1, Sunday=7. Return_type=3 makes Monday=0.

How do I check if a date is a weekend?

Use =WEEKDAY(A1, 2) > 5. TRUE for Saturday (6) or Sunday (7), FALSE for weekdays.

How do I show the day name instead of the number?

Use TEXT: =TEXT(A1, "dddd") returns 'Monday'; "ddd" returns 'Mon'. WEEKDAY always returns a number.