WEEKDAY Excel Function

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

WEEKDAY Formula in Excel

Below is the Excel WEEKDAY Formula.

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)

WEEKDAY - return_type =1

return_type =2

WEEKDAY - return_type =2

return_type =3

WEEKDAY - return_type =3

return_type ranging from 11 to 17

WEEKDAY - return_type ranging from 11 to 17

WEEKDAY - return_type ranging from 11 to 17.jpg 1

WEEKDAY - return_type ranging from 11 to 17.jpg 2

WEEKDAY in Excel Output:

WEEKDAY - return_type ranging from 11 to 17.jpg 3

How to Use WEEKDAY in Excel?

WEEKDAY function in excel is very simple and easy to use. Let me understand the working of WEEKDAY in excel by some examples.

You can download this WEEKDAY Function Excel Template here – WEEKDAY Function Excel Template

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.

=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”))))))

WEEKDAY Function 1

 Output:

WEEKDAY Function 2

=CHOOSE(WEEKDAY(A2),”Sun”,”Mon”,”Tue”,”Wed”,”Thur”,”Fri”,”Sat”)

WEEKDAY Function 3

Output: 

WEEKDAY Function 4

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”))))))

WEEKDAY Function 5
Output:

WEEKDAY Function 6

=CHOOSE(WEEKDAY(A2,2),”Mon”,”Tue”,”Wed”,”Thur”,”Fri”,”Sat”,”Sun”)

WEEKDAY Function 7

Output:

WEEKDAY Function 8

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”))))))

WEEKDAY Function 9

Output:

WEEKDAY Function 10

For return_type value 3, we cannot use the choose function because the Weekday function in Excel results in the 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”)

WEEKDAY Function 11

Output:

WEEKDAY Function 12

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.

WEEKDAY Function 13

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.

WEEKDAY Function 14

So, we will be using the IF condition along with the 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

WEEKDAY Function 15

Output:

WEEKDAY Function 16

WEEKDAY Function 17

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.

WEEKDAY Function 18

WEEKDAY Function 19
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,

WEEKDAY Function 20

Output:

WEEKDAY Function 21

The total payout amount is

=SUM(C2:C11)

Which is equal to $765.00

WEEKDAY Function 22

Things to Remember About WEEKDAY Function in Excel

  • By default, the return_type is always 1; that is, if we omit return_type, then the WEEKDAY function will take the default value is 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 the 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
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *