How to Use Excel WEEKDAY Function?
The Excel WEEKDAY Function returns the day of the week as the number
WEEKDAY Function: A Brief
The Excel WEEKDAY Function is used to return a number representing the day of the week for a specific date. This function is very helpful when you want to identify whether a date falls on a weekday or weekend or when automating schedules based on days.
| Objective | Value Returned by function |
|---|---|
| Aim to return a numerical weekday. | WEEKDAY Function will return a number of the day of the week from the given input date. |
WEEKDAY Function: A Syntax
=WEEKDAY(date_value, [return_type])- date_value: It should be a valid Excel Date value.
- return_type: A number that defines which day the week starts on and this is optional.
- WEEKDAY returns 1 for Sunday and 2 for Monday.
| Return_type | Day |
|---|---|
| 1 | Sunday |
| 2 | Monday |
| 3 | Tuesday |
| 4 | Wednesday |
| 5 | Thursday |
| 6 | Friday |
| 7 | Saturday |
Excel WEEKDAY function can number days of the week, based on the return_type argument.
- Which day is treated as the “first” day of the week, and
- What number gets assigned to each day (Sunday, Monday, etc.)
The return_type is optional and defaults to 1. It determines how the days of the week are numbered and which day is considered the first day. Each return_type code has its own mapping, showing the numeric result for each day and the starting day of the week in that scheme.
| Return Type | Mapping (Number = Day) | Week Starts On | Week Ends On |
|---|---|---|---|
| 1 or none | 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday. | Sunday | Saturday |
| 2 | 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday. | Monday | Sunday |
| 3 | 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday. | Monday (0-based) | Sunday |
| 11 | 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday. | Monday | Sunday |
| 12 | 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, 6 = Sunday, 7 = Monday. | Tuesday | Monday |
| 13 | = Wednesday, 2 = Thursday, 3 = Friday, 4 = Saturday, 5 = Sunday, 6 = Monday, 7 = Tuesday. | Wednesday | Tuesday |
| 14 | 1 = Thursday, 2 = Friday, 3 = Saturday, 4 = Sunday, 5 = Monday, 6 = Tuesday, 7 = Wednesday. | Thursday | Wednesday |
| 15 | 1 = Friday, 2 = Saturday, 3 = Sunday, 4 = Monday, 5 = Tuesday, 6 = Wednesday, 7 = Thursday. | Friday | Thursday |
| 16 | 1 = Saturday, 2 = Sunday, 3 = Monday, 4 = Tuesday, 5 = Wednesday, 6 = Thursday, 7 = Friday. | Saturday | Friday |
| 17 | 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday. | Sunday | Saturday |
Note: The WEEKDAY function in Excel will still return a result even if the date cell is empty. In such cases, it defaults to zero or may return 1, depending on how it’s used in the formula.
Basic Example:
This function can be very helpful if you are managing a small event calendar in Excel. Take a look at the below example.
| Date | Day Number | Day Name |
|---|---|---|
| 01-04-2025 | 3 | Tuesday |
| 03-04-2025 | 5 | Thursday |
| 06-04-2025 | 1 | Sunday |
| 07-04-2025 | 2 | Monday |
| 10-04-2025 | 5 | Thursday |
| Scenario | Formula Used |
|---|---|
| Formula for Finding Day Number | =WEEKDAY(B4, 1) |
| Formula for Finding Day Name | =TEXT(C4,”dddd”) |
From the example above, you can easily understand the basic use of the WEEKDAY function. I used the TEXT function along with the day number returned by WEEKDAY to show the day name (like Monday, Tuesday, etc.).
Using WEEKDAY Function with Nested Functions:
| Example Date Value | 01-04-2025 |
| S.No | Example Scenario That Needed Formula | Formula Used | Output. |
|---|---|---|---|
| 1 | Identify if the Date is a Weekend or Weekday | =IF(WEEKDAY(D3, 2)>5, “Weekend”, “Weekday”) | Weekday |
| 2 | Automatically Assign a Task Based on Day | =IF(WEEKDAY(D3, 2)=1, “Team Meeting”, IF(WEEKDAY(D3, 2)=5, “Weekly Report”, “Normal Work”)) | Normal Work |
| 3 | Skip Weekends and Show Next Working Day | =IF(WEEKDAY(D3,2)>5, D3+8-WEEKDAY(D3,2), D3+1) | 02 April 2025 |
| 4 | Show Message Only on Sundays | =IF(WEEKDAY(D3, 1)=1, “Run Weekly Backup”, “No the given date is not an Sunday”) | No the given date is not an Sunday |
| 5 | Highlight Friday Fun Day | =IF(TEXT(D3,”dddd”)=”Friday”, “Friday Fun!”, “Workday”) | Workday |
| 6 | Combine WEEKDAY with CHOOSE to Display Day Name | =CHOOSE(WEEKDAY(D3,1), “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”) | Tuesday |
| 7 | Calculate Due Date Skipping Sundays | =IF(WEEKDAY(D3,1)=1, D3+1, D3+0) | 01 April 2025 |
The explanation of the above examples are,
- The first formula returns “Weekday” because April 1, 2025 is a Tuesday, not a weekend day.
- The second formula returns “Normal Work”. Since April 1 is a Tuesday, it doesn’t match special conditions for Monday or Friday, so the default output is shown.
- The third formula gives “April 2, 2025”. As April 1 is a weekday (Tuesday), the next working day is simply the next day.
- The fourth formula shows “No, the given date is not a Sunday” because April 1 is a Tuesday, not Sunday, so the formula returns the response for a non-Sunday.
- The fifth formula returns “WorkDay”. It checks if the day is Friday. Since April 1 is a Tuesday, it returns “WorkDay” as expected.
- The sixth formula outputs “Tuesday”. The CHOOSE function uses the weekday number to return the correct day name, and April 1, 2025 is Tuesday.
- The seventh formula returns “01 April 2025”. The formula is designed to adjust the date only if it’s a Sunday. Since April 1 is not a Sunday, the same date is returned.
That’s it.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.
Reference Sources:
- Show dates as days of the week by Microsoft Support, Retrieved on 08/04/2025: Link.
- Excel Day of the Week Formula | WEEKDAY Function | Weekday Name – Monday to Sunday – from Date by Chester Tugwell, Retrieved on 08/04/2025: Link.
- WEEKDAY function not working at all by Excel Subreddit, Retrieved on 08/04/2025: Link.
- How to Use the WEEKDAY Function in Excel by Computergaga, Retrieved on 08/04/2025: Link.
- WEEKDAY function by Microsoft Support, Retrieved on 08/04/2025: Link.
- Excel Weekday Function by Syed Shah Zahurullah Hussaini, Retrieved on 08/04/2025: Link.
- What is a weekday formula in Excel? by Quora, Retrieved on 08/04/2025: Link.
