Weekday is an excel function which is used in excel to calculate the given weekday for the specified date, this function takes a date as an argument and a return type then returns an integer result ranging from 1-7 as there are seven days in a week, return type is an optional argument which when not provided then 1 is treated as default which represents Sunday and 7 is represented by Saturday, the method to use this function is =WEEKDAY( Serial Number, Return Value).
WEEKDAY Function in Excel
Excel Weekday function is categorized as Date/Time function. The WEEKDAY in Excel accepts a date argument and returns an integer between 1 and 7 that corresponds to the day of the week. The following Excel WEEKDAY formula, for example, returns 7 for a date – 04-August-2018.
=WEEKDAY(4/8/2018)
Output:
WEEKDAY Formula in Excel
Below is the Excel WEEKDAY Formula.
Explanation of WEEKDAY Function in Excel
WEEKDAY Formula in Excel takes two arguments:
serial_number: Required input and it a date value for which we want the day of the week
return_type: is an optional field, a value ranging from 1-17 is an argument that specifies the day numbering system for the result. If you specify 2 as the second argument, the WEEKDAY Excel returns 1 for Monday, 2 for Tuesday, and so on. If you specify 3 as the second argument, the function returns 0 for Monday, 1 for Tuesday, and so on. In Later versions of Excel
return_type =1 (default value that is if we didn’t pass an optional argument, the function takes default value as 1)
return_type =2
return_type =3
return_type ranging from 11 to 17
WEEKDAY in Excel Output:
How to Use WEEKDAY in Excel?
WEEKDAY function in excel is very simple and easy to use. Let understand the working of WEEKDAY in excel by some examples.
WEEKDAY in Excel Example #1
Determining the weekday name in excel for the value returned as an output using Weekday Function in Excel:
For a given date we can determine the weekday name in excel using given below Excel WEEKDAY Formula.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
=IF(WEEKDAY(A2)=1,”Sunday”,IF(WEEKDAY(A2)=2,”Monday”,IF(WEEKDAY(A2)=3,”Tuesday”,
IF(WEEKDAY(A2)=4,”Wednesday”,IF(WEEKDAY(A2)=5,”Thursday”, IF(WEEKDAY(A2)=6,”Friday”,”Saturday”)))))) |
Output:
=CHOOSE(WEEKDAY(A2),”Sun”,”Mon”,”Tue”,”Wed”,”Thur”,”Fri”,”Sat”)
Output:
The above WEEKDAY Formula in Excel work for the Weekday function with the return_type value as 1.
For return_type value 2, we have formulas
=IF(WEEKDAY(A2,2)=7,”Sunday”,IF(WEEKDAY(A2,2)=1,”Monday”, IF(WEEKDAY(A2,2)=2,“Tuesday”,IF(WEEKDAY(A2,2)=3,”Wednesday”, IF(WEEKDAY(A2,2)=4,”Thursday”,IF(WEEKDAY(A2,2)=5,”Friday”,“Saturday”)))))) |
Output:
=CHOOSE(WEEKDAY(A2,2),”Mon”,”Tue”,”Wed”,”Thur”,”Fri”,”Sat”,”Sun”)
Output:
For return_type value 3, we have the WEEKDAY formula in excel
=IF(WEEKDAY(A2,3)=6,”Sunday”,IF(WEEKDAY(A2,3)=0,”Monday”,
IF(WEEKDAY(A2,3)=1,”Tuesday”,IF(WEEKDAY(A2,3)=2,”Wednesday”, IF(WEEKDAY(A2,3)=3,”Thursday”,IF(WEEKDAY(A2,3)=4,”Friday”,”Saturday”)))))) |
Output:
For return_type value 3, we cannot use the choose function because the Weekday function in Excel results in first output as 0 for Monday and choose function first indexing starts from number 1.
Similarly, for other values of return_type, we can customize the Excel WEEKDAY Formula.
We can also use the TEXT function to display the weekday name in excel when a date is given
=TEXT(A2,”dddd”)
Output:
WEEKDAY in Excel Example #2 – Identify the Weekend Days
There is a list of random dates given in column A, we need to find the date is a weekend or a weekday.
We will be using the Weekday in excel to find which date is the weekend. We know that the serial number for Saturday and Sunday is 7 and 1.
So, we will be using the IF condition along with OR logical function to check if the weekday number is 1 or 7, then the day is weekend else the day is a Weekday
So, the WEEKDAY formula in excel will be
=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),”Weekend”,”Weekday”)
Applying the Excel WEEKDAY formula to other cells we have
Output:
Similarly, we can identify other weekday names in excel also be it a Monday, Tuesday or any other day.
WEEKDAY in Excel Example #3
We have the working hours of a freelancer who worked on different days, including weekends. If he works on a weekday the payout is $10/hour and if he works on a Saturday the payout amount is $15/hour. He worked for different hours each day (given below in table). We need to calculate his total payout amount.
The Weekday number value for Saturday is 7, so we will use the IF condition and check if the workday is a Weekday or Saturday and will compute the result accordingly.
So, the WEEKDAY formula in Excel that we will be using is
=IF(WEEKDAY(A2)=7,B2*$F$4,B2*$F$3)
Applying the WEEKDAY formula in excel to other cells we have,
Output:
The total payout amount is
=SUM(C2:C11)
Which is equal to $765.00
Things to Remember About WEEKDAY Function in Excel
- By default the return_type is always 1 that is if we omit return_type, then WEEKDAY function will take default value as 1.
- If the serial_number or return_type is out of range as specified above, #NUM! error is generated.
WEEKDAY Excel Function Video
Recommended Articles
This has been a guide to WEEKDAY in Excel. Here we discuss the WEEKDAY Formula in excel and how to use WEEKDAY function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion