Excel offers various functions that can make our daily work more efficient and less time-consuming. One such function is the WEEKDAY function. The Excel WEEKDAY function is used to return the day of the week as a number (1-7 or 0-6) based on a given date. This function is helpful in organizing data, analyzing trends, and performing calculations based on days of the week.
WEEKDAY Function Summary
Discover how to easily determine the day of the week with Excel’s WEEKDAY function. As a powerful DATE and TIME function, WEEKDAY returns an integer representing the weekday for any given date. This function is particularly valuable for financial analysis and other applications where accurate time tracking is crucial.
WEEKDAY Purpose
The purpose of the Excel WEEKDAY function is to identify the day of the week based on a given date. The function returns a number that represents the day of the week, where 1 represents Sunday, 2 represents Monday, and so on.
WEEKDAY Arguments
The WEEKDAY function has two arguments:
- Serial_number (required): This is the date or date value for which you want to determine the day of the week. You can enter a date directly in the function, such as “3/30/2023”, or refer to a cell that contains a date value, such as A1.
- Return_type (optional): This is an integer that specifies how you want the function to return the day of the week. The default value is 1, which returns a number from 1 (Sunday) to 7 (Saturday). You can also use other values for this argument to customize the output, as shown in the table I provided below.
WEEKDAY Syntax
The syntax for the Excel WEEKDAY function is:
Here’s what each part of the syntax means:
=WEEKDAY(serial_number, [return_type])- WEEKDAY: This is the name of the function you’re using.
- serial_number: This is a required argument that represents the date or date value for which you want to determine the day of the week.
- return_type: This is an optional argument that specifies the type of day-of-week numbering you want to use (as explained in the previous answers).
WEEKDAY Return Value
The Excel WEEKDAY function returns a number between 1 and 7 or 0 and 6, depending on the optional [Return_type] argument.
| Return_Type | Meaning |
|---|---|
| 1 or omitted | Returns a number representing the day of the week (1 = Sunday, 2 = Monday, 3 = Tuesday, and so on) |
| 2 | Returns a number representing the day of the week (0 = Monday, 1 = Tuesday, 2 = Wednesday, and so on) |
| 3 | Returns a number representing the day of the week (0 = Monday, 1 = Tuesday, 2 = Wednesday, and so on) with Sunday as day 7 |
The return_type argument in the WEEKDAY function determines how the function returns the day of the week as a number. By default or when return_type is omitted, the function returns a number from 1 to 7 that represents the day of the week, with 1 representing Sunday, 2 representing Monday, and so on.
However, when return_type is set to 2 or 3, the function returns a number that represents the day of the week, but with a different numbering scheme.
With return_type 2, the function returns a number from 0 to 6, with 0 representing Monday, 1 representing Tuesday, and so on. With return_type 3, the function returns a number from 0 to 6, with 0 representing Monday and 6 representing Sunday.
Alternate Day-of-Week Representations
These arguments provide various alternative ways of representing the day of the week for a given date, such as using abbreviated or full text representations of the day names.
| Return_Type | Meaning |
|---|---|
| 11 | Returns a number representing the day of the week (1 = Monday, 2 = Tuesday, 3 = Wednesday, and so on) |
| 12 | Returns a number representing the day of the week (1 = Tuesday, 2 = Wednesday, 3 = Thursday, and so on) |
| 13 | Returns a number representing the day of the week (1 = Wednesday, 2 = Thursday, 3 = Friday, and so on) |
| 14 | Returns a number representing the day of the week (1 = Thursday, 2 = Friday, 3 = Saturday, and so on) |
| 15 | Returns a number representing the day of the week (1 = Friday, 2 = Saturday, 3 = Sunday, and so on) |
| 16 | Returns a number representing the day of the week (1 = Saturday, 2 = Sunday, 3 = Monday, and so on) |
| 17 | Returns a number representing the day of the week (1 = Sunday, 2 = Monday, 3 = Tuesday, and so on) with Monday as day 1 |
| 21 | Returns a three-letter abbreviation of the day of the week (Sun, Mon, Tue, and so on) |
| 22 | Returns a full name of the day of the week (Sunday, Monday, Tuesday, and so on) |
As you can see, the WEEKDAY function offers a variety of ways to represent the day of the week, depending on the return_type argument you choose. These options can be useful in different contexts, such as financial analysis, scheduling, or reporting.
WEEKDAY Function Examples
Let’s consider an example to understand how the Excel WEEKDAY function works. Suppose we have a date, 5th July 2023.
To find out the day of the week for this date, we can use the WEEKDAY function as follows:
=WEEKDAY("5-July-2023")
This will return the number 4, which represents Wednesday. As we did not specify the return_type argument the start of the week was a Sunday.
If you wanted to use a different numbering scheme for the days of the week, such as starting with Monday as 1, you could specify the return_type value accordingly.
For example, to get the same result as above but with Monday as 1, you could use this formula:
=WEEKDAY("5-July-2023",2)
This will return the number 3, which represents Wednesday. However, as we specified a return_type argument of 2 our week started on a Monday.
Distinguishing Weekdays and Weekends with WEEKDAY Function
Suppose cell A1 contains the date value “2023-03-30”. To find out whether this date falls on a weekday or a weekend, you can use the following formula:
=IF(WEEKDAY(A1,2)<6,"Weekday","Weekend")
This formula uses the WEEKDAY function with a return_type value of 2 (which sets Monday as the first day of the week) to determine the day of the week for the date in cell A1.
If the day of the week is less than 6 (i.e., if it’s a Monday through Friday), the formula returns the text “Weekday”. Otherwise, it returns the text “Weekend”.
In this case, the formula would return the text “Weekday”, since March 30, 2023 falls on a Thursday.
WEEKDAY Notes
Here are some important points to keep in mind while using the Excel WEEKDAY function:
- If [Return_type] argument is not specified, the default value is 1, which means the function will return a number between 1 (Sunday) and 7 (Saturday).
- If [Return_type] is set to 2, the function will return a number between 1 (Monday) and 7 (Sunday).
- If [Return_type] is set to 3, the function will return a number between 0 (Monday) and 6 (Sunday).
- The WEEKDAY function is particularly useful when used in conjunction with other functions like SUMIFS, AVERAGEIFS, and COUNTIFS, to filter data by day of the week.