weekday

 The WEEKDAY function in Excel is used to return the day of the week corresponding to a date. By default, it returns a number from 1 (Sunday) to 7 (Saturday), but you can customize this behavior using different return types. Below is an explanation of the WEEKDAY function, including its syntax, examples, and expanded use cases with data and formulas.

---

Syntax of the WEEKDAY Function

excel

Copy

=WEEKDAY(serial_number, [return_type])

• serial_number: The date you want to find the day of the week for. This can be a cell reference, a date entered directly, or a formula that returns a date.

• return_type: (Optional) A number that determines the type of return value. If omitted, the default is 1.

---

Return Types

The return_type argument determines how the function maps days of the week to numbers:

Return Type	Description
1 or omitted	Numbers 1 (Sunday) to 7 (Saturday)
2	Numbers 1 (Monday) to 7 (Sunday)
3	Numbers 0 (Monday) to 6 (Sunday)
11	Numbers 1 (Monday) to 7 (Sunday)
12	Numbers 1 (Tuesday) to 7 (Monday)
13	Numbers 1 (Wednesday) to 7 (Tuesday)
14	Numbers 1 (Thursday) to 7 (Wednesday)
15	Numbers 1 (Friday) to 7 (Thursday)
16	Numbers 1 (Saturday) to 7 (Friday)
17	Numbers 1 (Sunday) to 7 (Saturday)

---

Examples

Example 1: Basic Usage

• Date: 2023-10-15 (October 15, 2023)

• Formula: =WEEKDAY("2023-10-15")

• Result: 1 (Sunday, because the default return type is 1)

Example 2: Custom Return Type

• Date: 2023-10-15 (October 15, 2023)

• Formula: =WEEKDAY("2023-10-15", 2)

• Result: 7 (Sunday, because return type 2 maps Monday as 1 and Sunday as 7)

Example 3: Using a Cell Reference

• Cell A1: 2023-10-15

• Formula: =WEEKDAY(A1, 11)

• Result: 7 (Sunday, because return type 11 maps Monday as 1 and Sunday as 7)

---

Expanded Use Cases

1. Highlight Weekends

You can use the WEEKDAY function to highlight weekends in a dataset:

• Formula for Conditional Formatting:

  excel

  Copy

  =OR(WEEKDAY(A1)=1, WEEKDAY(A1)=7)

  This formula will return TRUE if the date in cell A1 is a Saturday or Sunday.

2. Calculate Working Days

To calculate the number of working days between two dates, excluding weekends:

• Formula:

  excel

  Copy

  =NETWORKDAYS(start_date, end_date)

  This function automatically excludes Saturdays and Sundays.

3. Custom Weekday Mapping

If you want to map weekdays to custom numbers (e.g., Monday as 1, Tuesday as 2, etc.), use return type 2:

• Formula:

  excel

  Copy

  =WEEKDAY(A1, 2)

4. Find the Next Specific Weekday

To find the next Monday after a given date:

• Formula:

  excel

  Copy

  =A1 + (7 - WEEKDAY(A1, 2) + 1)

5. Group Dates by Weekday

You can group dates by their weekday using a pivot table and the WEEKDAY function:

• Add a helper column with the formula:

  excel

  Copy

  =WEEKDAY(A1, 2)

• Use this column in your pivot table to group dates by weekday.

---

Sample Data and Formulas

Date	Weekday (Default)	Weekday (Return Type 2)	Weekday (Return Type 11)
2023-10-15	=WEEKDAY(A2)	=WEEKDAY(A2, 2)	=WEEKDAY(A2, 11)
2023-10-16	=WEEKDAY(A3)	=WEEKDAY(A3, 2)	=WEEKDAY(A3, 11)
2023-10-17	=WEEKDAY(A4)	=WEEKDAY(A4, 2)	=WEEKDAY(A4, 11)

---

Key Notes

• The WEEKDAY function is useful for scheduling, reporting, and analyzing data based on days of the week.

• Combine it with other functions like IF, NETWORKDAYS, and CONDITIONAL FORMATTING for advanced use cases.