WEEKDAY Function

What is the WEEKDAY Function?

The WEEKDAY function in Excel returns a number between 1 to 7 corresponding to different days in a week from Monday to Sunday based on the input date.

Many organizations have different weekdays wherein some work from Monday to Friday, with Saturday and Sunday as weekly off, while others might work on weekends.

Some might even have a four-day working week from Monday to Thursday. If we want to find out the day of the week, irrespective of what weekday your organization follows, you have come to the right place.

The WEEKDAY function allows you to assume different days as a weekday starting point, wherein Monday can assume all seven values, i.e., 1, 2, 3, 4, 5, 6, and 7. 

Based on the numbers, it means that Monday can be the seventh day of the week and the first. This changes the perception of a work week based on how you want the week to start.

In this article, we will see the WEEKDAY function and how to use the function, along with a couple of examples.

WEEKDAY function Formula

The WEEKDAY is categorized as a Date and Time function that allows the user to return a number between 1 and 7 corresponding to different days of the week based on a given date.

The numbers aren’t assigned to each day. So, for example, if the date falls on Sunday, then it won’t always return the number as 7.

The function allows you to configure numbers so that each day of the week can be assigned a number between 1 and 7.

This can be done with the help of an additional argument that can be selected while returning the day for a given date.

For example, if the date is the 20th of October 2022 (44854) and you select Sunday as 1 through 7 (Saturday), the function returns the result as 5, meaning that the 20th of October falls on Thursday.

The syntax for the function is

=WEEKDAY(serial_number, return_type]

where

• serial_number - (required) represents the date value for finding the day of the week. 
• return_type - (optional) numbers assigned to different days of the week based on the mapping scheme.

By default, if you skip the return_type argument, the function assigns the numbers below to different weekdays:

The number corresponding to this mapping scheme in the function is 1. There are also other values that you can use to return different numbers for different days of the week.

The mapping scheme for different days can be understood by referencing the table below:

We see a general trend wherein each day assigned, all the numbers between 1 to 7. However, as we said earlier, each organization can have different weekdays; hence, this function fulfills that need by returning different days as the beginning of the weekday for them.

How to use the WEEKDAY Function in Excel?

The function is quite easy to use; it will generate a difference only when you use a different return_type argument for the function. In this section, we will see examples of how you can use the function in Excel.

1. Example #1

Suppose your organization has a four-day working week from Friday to Monday. This means that Fridays are the first working days in your organization.

You worked from the 14th of January 2023 to the 15th of March 2023 and need to determine how many first working days you have worked in the given time.

The snippet of data looks as illustrated below:

Here, we will use the formula =WEEKDAY(B3,15) in cell C3 and drag it down to the last cell of data which gives the result:

Since our work week begins on Friday, we use the value of 15 for the return_type argument.

Finally, select the entire data and create a pivot table that gives the total number of first working days from the 14th of January 2023 to the 14th of March 2023 as below:

As you can see, since Friday corresponds to our first work day, there are 8 Fridays or the first workdays from the 14th of January 2023 to the 14th of March 2023. 

Similarly, we can evaluate how many second or third working days are present in the given time using the pivot table created.

2. Example #2

Let’s take another simple example. Suppose you have some of the dates in Excel, as illustrated below:

Here too, we will use a similar formula =WEEKDAY(B3,11) but change the return_type argument to 11, which gives the result:

When the return_type corresponds to 11, all the dates falling on Monday are returned as 1 up to Sunday, which equals to 7.

Thus, based on this, you can determine that there are two Mondays in the given dataset by evaluating the numbers returned in column C.

WEEKDAY vs. WEEKNUM Function

If there’s a function that comes closest to what WEEKDAY does, it is the WEEKNUM function in Excel.

The WEEKNUM is categorized as a Date and Time function that returns the week number in the year for a given date.

For example, we know that most years will have 52 weeks except when the year starts on Thursday or is a leap year starting on Wednesday, then there are 53 weeks.

In such a case, if the date is 1st January 2022 and you still need to find the obvious answer as to what week the date falls in, you can use the WEEKNUM function in Excel.

The syntax for the function is

=WEEKNUM(serial_number, [return_type])

where,

• serial_number - (required) the date for which we need to find the week number
• return_type - (optional) numbers assigned to different weeks based on the mapping scheme. If the argument is ignored, the default value of 1 is assumed by Excel.

Suppose you have some dates in Excel as illustrated below:
 

To find what week each of the days falls in, we will use the formula =WEEKNUM(B3,1) in cell C3 and drag it down to cell C7, which gives the result:

If the question were to find the day of the week, the formula would be =WEEKDAY(B3,1), which gives the result in column D as:

This way, you can return either component in Excel, i.e., the day of the week or even the week of the year.

Researched and Authored by Akash Bagul | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• AND Function
• AVERAGE Function
• FV Function
• LEN Function
• YEAR Function