What is the WEEKDAY Function?

Master the WEEKDAY function to return the day of week as a number. Learn syntax, examples, and error solutions for Excel and Google Sheets calculations.

What is the syntax for WEEKDAY Function in Excel?

=WEEKDAY(serial_number, [return_type])

How to fix #N/A error in WEEKDAY Function?

The #N/A error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #REF! error in WEEKDAY Function?

The #REF! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #VALUE! error in WEEKDAY Function?

The #VALUE! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #DIV/0! error in WEEKDAY Function?

The #DIV/0! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #NAME? error in WEEKDAY Function?

The #NAME? error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

WEEKDAY Function

Quick Answer

Comprehensive Explanation

Syntax and Parameters

## What is the WEEKDAY Function?

The WEEKDAY function is one of Excel and Google Sheets' most practical date-time functions, designed to convert any date into its corresponding day of the week represented as a number. First introduced in early versions of Excel, it has become essential for professionals who need to perform day-based calculations, create automated scheduling systems, or analyze patterns based on weekdays versus weekends.

### Core Functionality

At its core, WEEKDAY operates by taking a date serial number (the internal numeric representation of dates in spreadsheets) and determining which day of the week it represents. The function then returns a number between 1 and 7, with the specific numbering depending on your chosen return type parameter. This numerical output makes it perfect for use in calculations, conditional formatting, and complex formulas where you need to treat different days differently.

### When to Use WEEKDAY

WEEKDAY is the ideal choice when you need to:

1. **Identify Weekend vs Weekday:** When working with business calculations that need to differentiate between working days and weekends, WEEKDAY provides the foundation for these distinctions.

2. **Create Day-Based Schedules:** In situations involving rotating schedules or day-specific tasks, this function enables automatic assignment based on the day of the week.

3. **Analyze Weekly Patterns:** For analyzing sales data, website traffic, or any metrics that vary by day of the week, WEEKDAY helps categorize and aggregate data effectively.

### Return Type Options

The WEEKDAY function offers multiple numbering systems through its return_type parameter:

- **Type 1 (Default):** Numbers 1 (Sunday) through 7 (Saturday) - Traditional US calendar format
- **Type 2:** Numbers 1 (Monday) through 7 (Sunday) - ISO standard and European format
- **Type 3:** Numbers 0 (Monday) through 6 (Sunday) - Alternative Monday-start format
- **Types 11-17:** Various other combinations for specialized regional or business needs

This flexibility ensures the function works seamlessly with different cultural conventions and business requirements worldwide.

## Syntax and Parameters Explained

### Parameters in Detail

#### serial_number/date (Required)
The date parameter is the heart of the WEEKDAY function. It accepts multiple input formats: direct date values like DATE(2025,9,25), cell references containing dates (A2), or date strings that Excel/Sheets can interpret ("9/25/2025"). The function is remarkably flexible in handling different date formats, automatically converting valid date strings into their serial number equivalents for processing.

#### return_type/type (Optional)
This parameter controls how days are numbered and which day is considered first. When omitted, it defaults to 1, using the Sunday=1 through Saturday=7 system. Here's a complete breakdown of all return types:

| Return Type | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
|------------|--------|---------|---------|-----------|----------|---------|----------|
| 1 or omitted | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 2 | 7 | 1 | 2 | 3 | 4 | 5 | 6 |
| 3 | 6 | 0 | 1 | 2 | 3 | 4 | 5 |
| 11 | 7 | 1 | 2 | 3 | 4 | 5 | 6 |
| 12 | 6 | 7 | 1 | 2 | 3 | 4 | 5 |
| 13 | 5 | 6 | 7 | 1 | 2 | 3 | 4 |
| 14 | 4 | 5 | 6 | 7 | 1 | 2 | 3 |
| 15 | 3 | 4 | 5 | 6 | 7 | 1 | 2 |
| 16 | 2 | 3 | 4 | 5 | 6 | 7 | 1 |
| 17 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |

Practical Examples

Common Errors and Solutions

#VALUE!

WEEKDAY returns #VALUE! error

Cause:

The date parameter contains text that cannot be interpreted as a valid date

Solution:

1. Verify the date format is recognizable (MM/DD/YYYY or DD/MM/YYYY depending on regional settings) 2. Check for extra spaces or hidden characters 3. Use DATE function to create valid dates: =WEEKDAY(DATE(2025,9,25)) 4. Ensure cells are formatted as dates, not text

#NUM!

WEEKDAY returns #NUM! error

Cause:

The return_type parameter is not a valid option (must be 1-3 or 11-17)

Solution:

1. Check that return_type is within valid range 2. Remove or correct the second parameter 3. Use default by omitting the parameter

Wrong Day Returned

WEEKDAY returns unexpected number for known day

Cause:

Using wrong return_type parameter for your expected numbering system

Solution:

1. Verify which return_type you're using 2. Check if you need Sunday-start (1) or Monday-start (2) 3. Adjust formula to match your requirements 4. Consider your regional date system expectations

Best Practices and Pro Tips

While WEEKDAY returns numbers, you can get day names directly using =TEXT(A2,"dddd") for full names or =TEXT(A2,"ddd") for abbreviations. This is often more user-friendly than numbers.

Apply conditional formatting rules based on WEEKDAY to automatically highlight weekends: =OR(WEEKDAY($A2)=1,WEEKDAY($A2)=7) creates weekend highlighting.

For large datasets, calculate WEEKDAY once in a helper column rather than repeatedly in complex formulas. This significantly improves spreadsheet performance.

Advanced Techniques

WEEKDAY vs Alternative Functions

## Advanced WEEKDAY Techniques

### Creating Custom Work Week Calculations

One of WEEKDAY's most powerful applications is creating custom work week definitions. Not all businesses operate Monday-Friday; some run Tuesday-Saturday, others work every day except Sunday. By combining WEEKDAY with logical functions, you can define any work pattern:

```excel
=SUMPRODUCT((WEEKDAY(date_range,2) 6)*(values_range))
```

This formula sums values only for Monday-Friday, but changing the comparison allows for any custom schedule.

### Fiscal Week Calculations

Many businesses operate on fiscal calendars where weeks might start on different days. WEEKDAY becomes essential for fiscal reporting:

```excel
=WEEKNUM(date,WEEKDAY(fiscal_year_start))
```

This adjusts week numbers based on when your fiscal year begins, ensuring accurate weekly reporting.

### Holiday Adjustment Logic

Combine WEEKDAY with holiday lists for sophisticated business day calculations:

```excel
=IF(OR(WEEKDAY(A2,2) 5,COUNTIF(Holidays,A2) 0),"Non-Working","Working Day")
```

This identifies both weekends and holidays, essential for accurate project scheduling and deadline calculations.

## Comparison with Alternative Approaches

| Feature | WEEKDAY | TEXT(,"dddd") | NETWORKDAYS | CHOOSE+WEEKDAY |
|---------|---------|----------------|-------------|----------------|
| Returns | Number (1-7) | Day name | Working days count | Custom output |
| Flexibility | High (multiple systems) | Limited | Business days only | Extremely high |
| Performance | Fastest | Moderate | Slower | Moderate |
| Locale-sensitive | No | Yes | No | No |
| Best for | Calculations | Display | Date ranges | Custom mappings |

### When to Use WEEKDAY

✅ **Choose WEEKDAY when:**
- You need numeric output for calculations
- Creating conditional logic based on day of week  
- Building scheduling or rotation systems
- Performance is critical with large datasets
- You need consistent behavior across different locales

### When to Use Alternatives

✅ **TEXT function when:**
- You need day names for display
- Localization is important
- User-friendly output is priority

✅ **NETWORKDAYS when:**
- Calculating business days between dates
- Holiday exclusion is required
- You need workday counts, not identification

✅ **CHOOSE+WEEKDAY when:**
- Custom output beyond simple day names
- Mapping days to specific values
- Creating complex scheduling logic