WEEKDAY: Day of Week (2025)
Quick Answer: WEEKDAY returns day number: =WEEKDAY(date) default 1=Sun to 7=Sat. Type 2 for Mon start: =WEEKDAY(date,2) where 1=Mon, 7=Sun. Weekend check: =WEEKDAY(date,2)>5. Day name: =TEXT(date,'dddd'). Business days: combine with NETWORKDAYS. Use for scheduling, conditional formatting, filtering.
What is WEEKDAY?
WEEKDAY returns the day of the week for a given date as a number (1-7). It's essential for scheduling, business day calculations, conditional formatting, and date analysis. The function offers different numbering systems to match various calendar conventions.
Quick Example:
Date: December 3, 2025 (Tuesday)
Type 1 (Default):
=WEEKDAY("12/3/2025")Result: 3 (Sun=1, Tue=3)
Type 2 (Monday start):
=WEEKDAY("12/3/2025", 2)Result: 2 (Mon=1, Tue=2)
Common Use Cases:
✓ Weekend Detection
Identify Saturdays and Sundays for scheduling
✓ Business Day Calculations
Exclude weekends from date analysis
✓ Conditional Formatting
Highlight specific days of the week
✓ Staff Scheduling
Assign tasks based on day of week
Return Type Comparison:
| Day | Type 1 (Default) | Type 2 (ISO) | Type 3 |
|---|---|---|---|
| Sunday | 1 | 7 | 6 |
| Monday | 2 | 1 | 0 |
| Tuesday | 3 | 2 | 1 |
| Wednesday | 4 | 3 | 2 |
| Thursday | 5 | 4 | 3 |
| Friday | 6 | 5 | 4 |
| Saturday | 7 | 6 | 5 |
Type 2 (Monday=1) most common for business calendars
Get Day Name (not just number):
Full Day Name
Result: "Tuesday"
Abbreviated Day
Result: "Tue"
WEEKDAY with CHOOSE
Result: "Tue"
💡 Pro Tip
Use Type 2 for business: =WEEKDAY(date, 2) where Monday=1 matches most business calendars and makes weekend detection easier (values 6-7).
For day names: TEXT function is simpler than WEEKDAY + CHOOSE for display purposes.
WEEKDAY Syntax
=WEEKDAY(serial_number, [return_type])
Arguments
serial_number (required)
Date to analyze
return_type (optional, default 1)
Numbering system to use
1 (default): 1=Sunday through 7=Saturday
2: 1=Monday through 7=Sunday (ISO 8601)
3: 0=Monday through 6=Sunday
11-17: Various week start days (1=starting day)
Common Return Types
Type 1 (Default - US Standard)
=WEEKDAY(date)
1=Sun, 2=Mon, 3=Tue, 4=Wed, 5=Thu, 6=Fri, 7=Sat
Type 2 (ISO 8601 - Business Standard)
=WEEKDAY(date, 2)
1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun
Type 3 (Zero-based Monday start)
=WEEKDAY(date, 3)
0=Mon, 1=Tue, 2=Wed, 3=Thu, 4=Fri, 5=Sat, 6=Sun
Weekend Detection Formulas
With Type 1 (Default)
TRUE if Sunday (1) or Saturday (7)
With Type 2 (Easier!)
TRUE if Saturday (6) or Sunday (7)
Weekday Check (Type 2)
TRUE if Monday-Friday
Extended Return Types (11-17)
Start Week on Specific Day:
- • 11: 1=Monday (same as type 2)
- • 12: 1=Tuesday
- • 13: 1=Wednesday
- • 14: 1=Thursday
- • 15: 1=Friday
- • 16: 1=Saturday
- • 17: 1=Sunday (same as type 1)
Rarely used - types 1-3 cover most scenarios
⚠️ Important Notes
- • Type 2 recommended: Most intuitive for business use (Monday=1)
- • Returns number: Not day name - use TEXT for names
- • Date validation: Invalid dates return #VALUE! error
- • Works with any date: Past, present, or future
- • Combine with IF: Perfect for conditional logic
- • TODAY() compatibility: Use with TODAY() for current day checks
- • Consistent numbering: Same date always returns same number for given type
10+ WEEKDAY Examples
Weekend & Weekday Detection
1. Is it Weekend?
=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")
Type 2: Sat=6, Sun=7
2. Count Weekends in Range
=SUMPRODUCT((WEEKDAY(A2:A100,2)>5)*1)
Counts Saturday and Sunday dates
3. Filter Weekdays Only
=FILTER(A2:B100, WEEKDAY(A2:A100,2)<=5)
Returns only Monday-Friday rows (Excel 365)
Specific Day Detection
4. Is it Monday?
=WEEKDAY(A2,2)=1
TRUE if Monday (type 2)
5. Find All Fridays
=FILTER(A2:A100, WEEKDAY(A2:A100,2)=5)
Returns all Friday dates
6. Count Mondays in Month
=SUMPRODUCT((MONTH(A2:A100)=1)*(WEEKDAY(A2:A100,2)=1))
Mondays in January
Conditional Formatting
7. Highlight Weekends
=WEEKDAY($A1,2)>5
Conditional format rule for weekends
8. Color Code by Day
=WEEKDAY($A1,2)=1
Format Mondays differently (create multiple rules)
Business Logic
9. Adjust Due Date (Skip Weekend)
=IF(WEEKDAY(A2,2)>5, A2+IF(WEEKDAY(A2,2)=6,2,1), A2)
If weekend, push to Monday
10. Weekend Surcharge
=B2*IF(WEEKDAY(A2,2)>5, 1.5, 1)
150% rate on weekends
11. Day Name from WEEKDAY
=CHOOSE(WEEKDAY(A2,2),"Mon","Tue","Wed","Thu","Fri","Sat","Sun")
Convert number to abbreviated name
🎯 Pro Tips
- • Type 2 for business: Easier weekend detection (values > 5)
- • Combine with NETWORKDAYS: For accurate business day calculations
- • Conditional formatting: Use absolute column reference ($A1) for rows
- • TEXT for names: =TEXT(date,"dddd") simpler than CHOOSE method
- • TODAY() integration: =WEEKDAY(TODAY(),2) for current day checks
- • Array formulas: Works with FILTER, SUMPRODUCT for bulk operations
- • Data validation: Use with IF to prevent weekend date selection
Frequently Asked Questions
What's the easiest way to detect weekends with WEEKDAY?
Use return_type 2: =WEEKDAY(date,2)>5 returns TRUE for weekends. Type 2 numbers Monday=1 through Sunday=7, so Saturday (6) and Sunday (7) are both >5. Alternative with default type: =OR(WEEKDAY(date)=1, WEEKDAY(date)=7) but Type 2 is simpler. For conditional formatting: =WEEKDAY($A1,2)>5 with absolute column reference.
How do I get the day name instead of a number?
Use TEXT function: =TEXT(date,'dddd') for full name ('Monday'), =TEXT(date,'ddd') for abbreviated ('Mon'). Alternative with WEEKDAY: =CHOOSE(WEEKDAY(date,2),'Mon','Tue','Wed','Thu','Fri','Sat','Sun') but TEXT is simpler. For custom names: use CHOOSE with your own labels. TEXT respects Excel language settings.
Which return_type should I use?
Type 2 (Monday=1) recommended for most business use. Matches ISO 8601 standard, easier weekend detection (>5), intuitive for Monday-Friday work weeks. Type 1 (default, Sunday=1) for US calendar conventions. Type 3 (Monday=0) for zero-based indexing needs. Types 11-17 rarely used. Stick with Type 2 for consistency unless specific requirement.
Can WEEKDAY work with date ranges for bulk operations?
Yes with array formulas. Count weekends: =SUMPRODUCT((WEEKDAY(A2:A100,2)>5)*1). Filter weekdays (Excel 365): =FILTER(A:B, WEEKDAY(A:A,2)<=5). Conditional formatting: Apply to range with =WEEKDAY($A1,2)>5. SUMPRODUCT, FILTER, COUNTIFS all support WEEKDAY in arrays. Efficient for large datasets.
How do I skip weekends in date calculations?
Use WORKDAY or WORKDAY.INTL instead of WEEKDAY for automatic weekend skipping. WORKDAY: =WORKDAY(start, days) adds business days only. WORKDAY.INTL: customize weekend days. If must use WEEKDAY: =IF(WEEKDAY(date,2)>5, date+IF(WEEKDAY(date,2)=6,2,1), date) pushes Sat to Mon, Sun to Mon. But WORKDAY functions are designed for this purpose.
Does WEEKDAY account for holidays?
No, WEEKDAY only knows days of week, not holidays. Use NETWORKDAYS or NETWORKDAYS.INTL for holiday-aware business day calculations. NETWORKDAYS: =NETWORKDAYS(start, end, holidays_range) excludes weekends AND specified holidays. WEEKDAY best for: day-of-week logic, scheduling patterns, conditional formatting. Use NETWORKDAYS for: business day counting, project timelines, deadline calculations with holidays.