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.
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.
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:
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.
–Narendhiran Vijayakumar. This tutorial is originally published on How to use Excel Weekday Function?
