Excel WEEKDAY Function

The weekday function is an excel function used to return the day of the week of any given date. It returns a number from 1 – 7, where 1 represents Sunday and 7 represents Saturday. What is the point of this function you ask. Well, there are so many uses, and one vital to financial modeling is when it is used for analyzing sales data by week. For example, using the WEEKDAY function to find the day of the week for each sale, and then analyze the performance of sales for each day.

To reiterate, the WEEKDAY Function is a useful tool for organizing data by weekday or for scheduling tasks based on the weekday. In this post, we would walk you through all there is to know about the WEEKDAY function, and we would be touching on various examples including the example on finding the day of the week for each sale and analyzing the performance of sales for each day. But let’s first start with the Syntax and argument for the WEEKDAY Function.

Syntax & Argument

The Syntax of the WEEKDAY Function is as follows:

=WEEKDAY(serial_number, [return_type])

There are two arguments for the WEEKDAY function, one is required whilst the other is optional. Let’s see which is which below.

• Serial_number: This is the date you want to return the day of the week for. This must be a date unless the function returns a #VALUE error.
• return_type: This is an optional argument that allows you specify the day of the week considered as the first day. For example, as at the time of making this post, I am working in the middle east where the first day of the week is considered to be Saturday.

Now, let’s move on to some usage notes for the WEEKDAY Function.

Usage Notes

In this section, we would be touching on some of the important usage notes to keep in mind when using the WEEKDAY Function.

• The Serial_number argument must be a valid excel serial number that represents a date. This could be in the form of a date entered directly into the function, or a reference to a cell that contains a date.
• The return_type argument is optional, however it is important to use if the first day of the week in the region you are staying is different from the usual Sunday. As stated in the syntax & argument section, the first day of the week in the country I am currently residing is Saturday, so when using this function, I would need to use 16 as my return_type argument. There is no need to memorize the numbers, once you type in a number for the return_type argument, excel shows a drop down that states the first day of the week and their respective numbers.
• The WEEKDAY Function returns a number when used on blank cells because blank cells are taken to be 0 and this number, weirdly, is given the date: 1/0/1900. So take care to ensure that you dont have blank dates when using this function.
• The WEEKDAY function can be combined with other functions such as the IF function in order to create more complex formulas. For example, you can return the value for a date and use the IF Function to return another value if the value for the date is a particular number.
• By default, the WEEKDAY returns 1 for Sunday and 7 for Saturday if the return_type argument is not specified by the user. The table below shows the default number for each day of the week.

In the usage note, we stated that for a first day of the week of Saturday, we would use the number 16 as our return_type argument. Let’s see some other day mapping for various first days of the week.

Now that we are done with the usage notes, we can move on to some examples. We would start with simple examples and then move on to more complex examples.

WEEKDAY Function Examples

Hardcoded Dates

As stated in the usage notes section, the WEEKDAY Function assumes a default week start date of Sunday if the return_type is not provided by the user.

Now let’s include the return_type to match the region I currently reside in, where the first day of the week is Saturday:

Dates from Cell reference

In this section we would be selecting the dates from another cell. We have 3 dates to test, an let’s see the numbers that would be returned assuming a start day of Sunday on one hand and a start day of Saturday on the other hand.

For our Sunday Start column, we use the formula below:

For our Saturday Start column, we would introduce the return_type argument which is 16 in this case.

Combining WEEKDAY & IF Functions

In the introduction of this function, we talked about using the WEEKDAY Function to find the day of the week for Sales. In this section, we would be doing just that with the help of a combination of other functions such as the IF and OR functions. But first, let’s introduce you to our data set for this section.

As you can see, we have a bunch of dealers reporting sales on different days within a 2 month span June – July. These sales were actually made during those days. In our business we sell high value, luxurious goods and we want to know where we should channel more of our marketing budget to. For this, we need to compare the average value of sales during the weekdays versus the average value of sales during the weekends.

In the category section, we are going to combine the WEEKEND Function, OR Function and IF Function to return weekday or weekend depending on the date in the Day column (Column B). To achieve this, we would first type in the formula below in cell E4. Please note that we are using 16 here because our first day of the week is Saturday. If your first day of the week is the usual Sunday, please change the 16 to 1 or better still, leave it blank and have your closing bracket immediately after $B4

Now copy and paste the formula to the cells below using Ctrl + C to copy and pasting with Ctlr + V

Great job, but we are not done yet. We need to now analyze the data with the new data points (Category) added. Let’s use the AVERAGEIF Function to return the average sales of both weekend and weekday sales.

To return the average weekday sales we use the formula below:

You can copy and paste for the weekend sales, the only thing we need to change is the criteria. We would change the criteria from “Weekday” to “Weekend”.

Download Excel File