WEEKDAY Function
Returns an integer that represents the day of the week for a given date
Returns an integer that represents the day of the week for a given date
The WEEKDAY Function is a DATE and TIME Function. The function will return an integer that is a representation of the day of the week for a given date.
WEEKDAY is quite useful in financial analysis. Suppose we wish to determine the time required to complete a certain project, the function can be useful in removing weekends from the given time frame. Thus, it is particularly useful when planning and scheduling work for the business projects.
=WEEKDAY(serial_number,[return_type])
It uses the following arguments:
Return_type | Number returned |
---|---|
1 or omitted | It will take Sunday as first day of week so, Sunday = 1, Monday = 2, ... , Saturday = 7 |
2 | It will take Monday as first day of week so, Monday = 1, Tuesday = 2, ... , Sunday = 7 |
3 | It will take Tuesday as first day of week so, Monday = 0, Tuesday = 1, ... , Sunday = 6 |
11 | Monday = 1, Tuesday = 2, ... , Sunday = 7 |
12 | Tuesday = 1, Wednesday = 2, ... , Monday = 7 |
13 | It will take Wednesday as first day of week so, Wednesday = 1, Thursday = 2, ... , Tuesday = 7 |
14 | It will take Thursday as first day of week so, Thursday = 1, Friday = 2, ... , Wednesday = 7 |
15 | It will take Friday as first day of week so, Friday = 1, Saturday = 2, ... , Thursday = 7 |
16 | It willl take Saturday as first day of week so, Saturday = 1, Sunday = 2, ... , Friday = 7 |
17 | Sunday = 1, Monday = 2, ... , Saturday = 7 |
It is a built-in function that can be used as a worksheet function in Excel. To understand the uses of WEEKDAY, let us consider a few examples:
Let’s see how we can get weekends and weekdays highlighted. It can be done using conditional formatting rules. Using the data below:
Using the WEEKDAY formula, we can find out if the day is a weekday or weekend. The formula to use will be:
We get the results below:
Here, we’ve taken the return_type as 1 as we want Sunday to be the first day of the week. So, here a Friday would be a weekend as per this formula.
Now to improve the presentation of the data, we can use conditional formatting to highlight weekdays, which is the working days and weekends, in different colors.
We will create two rules in conditional formatting where the first rule would be WEEKDAY($A2,1)<6 and second rule would be =WEEKDAY($A2,1)>5.
We get the results below:
If we wish to get the day names in any format of our choice, we can use the WEEKDAY function along with the CHOOSE function. Using the data below:
Now we want the day to be displayed as Sun, Mon, Tue, Wed, Thur, Fri and Sat. The formula to use will be:
In the formula above, the WEEKDAY function will get the day of week as a serial number. Then, the CHOOSE function will use that number as index_num (the first argument) that indicates which value from the list of value arguments to return. The results we get are shown below:
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!