The WEEKDAY function in Excel returns a number that represents the day of the week for a given date. The number ranges from 1 to 7, depending on the return type argument that you specify. By default, the return type is 1, which means that Sunday is 1 and Saturday is 7. You can use the WEEKDAY function to determine whether a date falls on a weekend or not, by comparing the result with 1 or 7.
Basic Syntax
The basic syntax of the WEEKDAY function is:
=WEEKDAY(date, [return_type])
dateis the date that you want to get the weekday for. It can be a cell reference, a date value, or a formula that returns a date.[return_type]is an optional argument that specifies how the weekdays are numbered. It can be one of the following values:
| Return type | Weekday numbering | Example |
|---|---|---|
| 1 (default) | Sunday = 1, Monday = 2, …, Saturday = 7 | =WEEKDAY(“2024-02-09”) returns 6 |
| 2 | Monday = 1, Tuesday = 2, …, Sunday = 7 | =WEEKDAY(“2024-02-09”, 2) returns 5 |
| 3 | Monday = 0, Tuesday = 1, …, Sunday = 6 | =WEEKDAY(“2024-02-09”, 3) returns 4 |
| 11 | Monday = 1, Tuesday = 2, …, Sunday = 7 (same as 2) | =WEEKDAY(“2024-02-09”, 11) returns 5 |
| 12 | Tuesday = 1, Wednesday = 2, …, Monday = 7 | =WEEKDAY(“2024-02-09”, 12) returns 4 |
| 13 | Wednesday = 1, Thursday = 2, …, Tuesday = 7 | =WEEKDAY(“2024-02-09”, 13) returns 3 |
| 14 | Thursday = 1, Friday = 2, …, Wednesday = 7 | =WEEKDAY(“2024-02-09”, 14) returns 2 |
| 15 | Friday = 1, Saturday = 2, …, Thursday = 7 | =WEEKDAY(“2024-02-09”, 15) returns 1 |
| 16 | Saturday = 1, Sunday = 2, …, Friday = 7 | =WEEKDAY(“2024-02-09”, 16) returns 7 |
| 17 | Sunday = 1, Monday = 2, …, Saturday = 7 (same as 1) | =WEEKDAY(“2024-02-09”, 17) returns 6 |
Example
Suppose you have a list of dates in column A, and you want to mark the dates that are weekends with “Yes” in column B. You can use the WEEKDAY function with an IF function to achieve this. Here are the steps:
- In cell B2, enter the formula
=IF(WEEKDAY(A2,2)>5,"Yes","No"). This formula checks if the weekday number of the date in A2 is greater than 5, which means it is either Saturday or Sunday, according to the return type 2. If so, it returns “Yes”, otherwise it returns “No”. - Copy the formula down to the rest of the cells in column B.
- You should see the results as shown in the table below:
| Date | Weekend |
|---|---|
| 2024-02-01 | No |
| 2024-02-02 | Yes |
| 2024-02-03 | Yes |
| 2024-02-04 | No |
| 2024-02-05 | No |
| 2024-02-06 | No |
| 2024-02-07 | No |
| 2024-02-08 | No |
| 2024-02-09 | Yes |
| 2024-02-10 | Yes |
Other Approaches
There are other ways to use the WEEKDAY function to determine the weekend, depending on your preference and needs. Here are some examples:
- You can use the return type 1 and compare the result with 1 or 7, instead of 2 and 6. For example,
=IF(WEEKDAY(A2,1)=1,"Yes",IF(WEEKDAY(A2,1)=7,"Yes","No")). - You can use the return type 3 and compare the result with 0 or 6, instead of 2 and 6. For example,
=IF(WEEKDAY(A2,3)=0,"Yes",IF(WEEKDAY(A2,3)=6,"Yes","No")). - You can use the OR function to simplify the comparison. For example,
=IF(OR(WEEKDAY(A2,2)>5),"Yes","No"). - You can use the CHOOSE function to assign custom labels to the weekdays. For example,
=CHOOSE(WEEKDAY(A2,2),"Mon","Tue","Wed","Thu","Fri","Weekend","Weekend").
