Excel Formula for Weekday (Table of Contents)
Excel Formula for Weekday
Hello guys, how do you tell what is the weekday by just looking at the date in your excel? You probably would tell the day of your birth or your marriage date, or any special events. Human beings’ memory power is limited, and we cannot tell what the day name is by instantly looking at the date.
In excel, we have several ways to find the day of the week. In this article, we will discuss the different ways of calculating the Excel Formula for Weekday.
How to Find Day of Week using Weekday Formula in Excel?
Excel Formula for Weekday is very simple and easy. Let’s understand the working of how to find a day of the week using the weekday formula in excel.
Example #1
Get Day of the Week by Changing the Format of the Cell
We know excel numbers work with the format of the cell. We can get the day of the date by changing the format of the date. Take a look at the below date examples.
How can you identify what is the name of the days for these dates, whether it is Monday or Tuesday or some other day? Yes, we cannot just by looking at it.
Step 1: Select the date range in excel.
Step 2: Under the HOME tab, we have a number format section click on the drop down.
Step 3: Select Long Date from the drop-down list.
Step 4: Now, we have all the dates formatted with day name, month name, and date and year as well.
Weekday Formula
How do you tell the day of the week by looking at the date? For example, if you have dates from 07th Jan 2019 to 13th Jan 2019, how do you tell what is the day number of the date 10th Jan 2019 if the start of the week is from Monday. How do you tell the weekday number of the date 10th Jan if the start of the week is from Tuesday?
Yes, we can tell all these by using the built-in function called WEEKDAY in excel. Let’s look at the syntax of the WEEKDAY function.
- Serial Number: This is nothing but the date you are looking to find the weekday number.
- Return Type: This is an optional argument. You need to tell excel what is the starting day of the week. If you ignore this by default, excel takes the argument as Week starting date as Sunday.
We can choose our own start of the weekday by choosing any one of the following return types.
Example #2
Find Week by Using Weekday Excel Function
Now take a look at the below example of using the WEEKDAY function in excel. Take the below dates and find what the day number is in the week.
Step 1: Open the WEEKDAY function in the B2 cell.
Step 2: Serial number is nothing but your date that you are finding the day number. So select the date cell as A2.
Step 3: Now, you need to select the starting day of the week. I am selecting Monday as the starting day of the week. In order to select the starting day of the week, we can mention numbers as shown in the below image.
Step 4: Close the bracket and hit enter; we have the day number in the week.
Note: Make sure the number format is General.
Step 5: Drag the formula to other cells to get the results.
So 30th Nov 2015 is the first day of the week if the starting day is Monday. We can have resulted from 1 to 7 because we have only 7 days in a week.
Example #3
Find Weekend Date by Using Weekday Function
We can find the next weekend date by considering any date in excel. Take a look at the below dates for this example.
Now 10th May 2018 is on Thursday, so the weekend date is on 12th May 2018, i.e., Saturday. Like this, how do you tell the weekend starting date based on the date if the weekday is starting from Monday?
We can tell the next weekend date with the combination of WEEKDAY & IF condition in excel.
Step 1: Open the IF condition in the B2 cell.
Step 2: Open the WEEKDAY function inside the IF condition and select the date as an A2 cell. Week starting date is from Monday, i.e., 2 is the criteria.
Step 3: Now test if the Weekday function can return 1 as a result.
Step 4: If the WEEKDAY result is equal to 1, i.e., Monday, so add five more days to the existing date; it will become Saturday.
Step 5: Now again, test if the WEEKDAY function can return 2 as a result. If it returns 2; as a result, i.e., Tuesday from Tuesday, add 4 more days to get the Weekend Date.
Step 6: Like this test, all the conditions until WEEKDAY Function returns 5 as a result and add one less day to the existing date to get the weekend date.
Step 7: Now, we have set all the weekdays, but the date is already a weekend date. So if all these results are FALSE, mention the result as “This is the Weekend Date.”
Step 8: Hit enter, and we get the result.
Step 9: Drag the formula to other cells to get the results.
Below is the formula I have used to find the next weekend date.
=IF(WEEKDAY(A2,2)=1,A2+5, IF(WEEKDAY(A2,2)=2,A2+4, IF(WEEKDAY(A2,2)=3,A2+3, IF(WEEKDAY(A2,2)=4,A2+2, IF(WEEKDAY(A2,2)=5,A2+1, “This is the Weekend Date”)))))
Example #4
Find Week Day Name by Using Weekday & Choose Function
We have seen the WEEKDAY function returns the day number in the week. But if we want the name of that day, how do you get it. We can use CHOOSE + WEEKDAY function to get the weekday name based on the number given by the weekday function.
Below is the formula which can give us the desired result.
The Result will be as given below.
Drag the formula to other cells to get the results.
Here WEEKDAY function returns the day number in the week. If weekday returns 1 as a result, CHOOSE function chooses Mon as the result because that is the 1st value we have passed to the argument if weekday returns 2, CHOOSE function chooses Tue; as a result, i.e., the second value in the list. Like this, based on the number given by the WEEKDAY function, CHOOSE function returns the result.
Things to Remember
- WEEKDAY function returns day number in the week only.
- We can choose our own week starting date by entering an appropriate parameter to the return type argument in the WEEKDAY function.
- TEXT function also can find the day name from the date.
- The WEEKDAY function can help us to find the weekend date with the combination of an IF condition.
Recommended Articles
This has been a guide to Excel Formula for Weekday. Here we discuss how to find a day of the week using Weekday Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
