Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use WEEKDAY Function in Excel (with 8 Examples)

If you are looking for Excel WEEKDAY function, then you are in the right place. The WEEKDAY function is especially handy for planning, scheduling, and even financial analysis. It is classified as a Date and Time Functions in Excel which returns the day of the week for a given argument.
In this article, we’ll try to discuss the WEEKDAY function’s ins and outs, including real-life examples with proper explanations. So that you may adjust the formula for your purpose.

WEEKDAY Function in Excel (Quick View)

Excel WEEKDAY Function


Download Excel Workbook


Excel WEEKDAY Function: Syntax & Arguments

Syntax

WEEKDAY Formula

=WEEKDAY(serial_number, [return_type])

Return Values

0 to 7 (in numbers)

Arguments

Argument Required/Optional Explanation
serial_number Required A number representing the day from which you want to find the day of the week
return_type Optional A number determining the return value type

Note:

  1. Dates are stored in Microsoft Excel as consecutive serial numbers, which allows them to be used in calculations. Besides, dates should be inserted as the date format or using the DATE function. There may be problems if the dates are inserted in text format.
  2. Return_type is an argument in the WEEKDAY function which specifies the first day of the week. If you omit to input any return_type, the WEEKDAY function returns 1 for Sunday and 7 for Saturday (by default). Return type 11-17 is introduced in Excel 2010 version.
Return_type First day Numerical Result Start-end
1 or omitted (default) Sunday 1-7 Sunday-Saturday
2 Monday 1-7 Monday-Sunday
3 Tuesday 0-6 (since Monday = 0 in this case) Monday-Sunday
11 Monday 1-7 Monday-Sunday
12 Tuesday 1-7 Tuesday-Monday
14 Thursday 1-7 Thursday-Wednesday
15 Friday 1-7 Friday-Thursday
16 Saturday 1-7 Saturday-Friday
17 Sunday 1-7 Sunday-Saturday

8 Examples to Understand and Use WEEKDAY Function in Excel

Excel offers different applications using the WEEKDAY function. We’ll try to discuss those as examples.


Example 1: Basic Examples of WEEKDAY Function

If the days are given in date format and do not need to input the return_type value, you may utilize the following formula in the D5 cell.

=WEEKDAY(C5)

Here, C5 is the Joining Date of Robert.

Basic Examples of WEEKDAY Function

  • Secondly, press ENTER.
  • Thirdly, use the Fill Handle by dragging down the cursor while holding it at the right-bottom corner of the D5 cell like this.

Excel WEEKDAY Function

  • Eventually, the outputs will be like this.

But if the return type is 2 and 16 respectively for the same dataset. You may use the following formula in the D5 cell.

  • When the value of return_type is 2:
=WEEKDAY(C5,2)

Excel WEEKDAY Function

  • When the value of return_type is 16:
=WEEKDAY(C5,16)

Read More: How to Use DATEVALUE Function in Excel (6 Suitable Examples)


Example 2: Using WEEKDAY Function with DATE Function

Let’s imagine that the joining date is given in serial number. In that case, you have to use the DATE where the YEAR, MONTH, and DAY functions are used to return the year, month, and day of the given date respectively.

  • Eventually, just use the following formula in the D5
=WEEKDAY(DATE(YEAR(C5),MONTH(C5),DAY(C5)),12)

Using WEEKDAY Function with DATE Function

  • Secondly, press ENTER.
  • Thirdly, use the Fill Handle.

Excel WEEKDAY Function

  • Consequently, you’ll get the output like this.

Excel WEEKDAY Function

Read More: How to Use DATEDIF Function in Excel (2 Ideal Examples)


Example 3: Using TEXT Function to Find Weekday Name

If you want to get the name of the day of the week that is found using the WEEKDAY function, you may use the TEXT function.

  • Ultimately, you need to write the formula in the D5 cell like this.
=TEXT(WEEKDAY(C5,2),"dddd")
  • Similarly, after pressing ENTER and then using the Fill Handle, the output will be like this.

Using TEXT Function to Find Weekday Name

Read More: How to Use WEEKNUM Function in Excel (2 Ways)


Similar Readings


Example 4: WEEKDAY Combined with CHOOSE Function

Besides, there is another way to find the name of the weekday. You can use the CHOOSE function with the WEEKDAY function.

  • Just use the following formula in the D5 cell and then the Fill Handle like we have done before to get all the outputs like this.
=CHOOSE(WEEKDAY(C5),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

WEEKDAY Combined with CHOOSE Function

Read More: How to Use EDATE Function in Excel (5 Simple Examples)


Example 5: Combined with SWITCH Function

Again, you may use the SWITCH function to get the name of the weekday with the WEEKDAY function.

  • The formula in the D5 cell will be like this.
=SWITCH(WEEKDAY(C5,1),1,"Sun",2,"Mon",3,"Tue",4,"Wed",5,"Thu",6,"Fri",7,"Sat")
Combined with SWITCH Function

Read More: How to Use DAY Function in Excel (3 Ideal Examples)


Example 6: WEEKDAY Function to Get Weekdays and Weekends

If you want to determine the weekdays and weekends for a given date, you may utilize the formula which is the combination of IF and WEEKDAY functions.

=IF(WEEKDAY(C5,2)<6,"Workday","Weekend")

WEEKDAY Function to Get Weekdays and Weekends

Read More: How to Use NETWORKDAYS Function in Excel (3 Suitable Examples)


Similar Readings


Example 7: WEEKDAY Function with Conditional Formatting

If you need to highlight the workday and weekend for better visualization, you may use the Conditional Formatting toolbar from the Styles command bar.

Steps:

  • Firstly, select the cells B5:D12.
  • Secondly, go to Home > choose Conditional Formatting > select New Rule.

WEEKDAY Function with Conditional Formatting

  • Eventually, a New Formatting Rule window will appear.
  • Thirdly, choose Use a formula to determine which cells to format.
  • Fourthly, write the formula in the formula box.
=WEEKDAY($C5,2)<6
  • Fifthly, go to Format to select the color and other things of the output if necessary.

Excel WEEKDAY Function

  • In the Format Cells window, go to Fill >choose a color (Here, it is light Green) > click OK.

  • Again, in the New Formatting Rule, click OK.

Excel WEEKDAY Function

  • Similarly, again go to New Formatting Rule after selecting the cells. This time write the formula like this.
=WEEKDAY($C5,2)>5
  • Then, go to Format.

  • Eventually, select any color in the Fill option. We have selected light blue color
  • Click, OK.

  • Lastly, click OK in the New Formatting Rule

Excel WEEKDAY Function

  • Consequently, the outputs will be like this.

Excel WEEKDAY Function

Read More: How to Use the Excel DAYS Function with a Practical Example


Example 8: Calculating Payment Including Weekends

At last, we’ll see an exciting example. Assuming you have a project to finish within a deadline, and you have to manage your employee to do extra work on working days and weekends.
Usually, the rate of payment is higher for weekends. If the number of working hours is given, you need to calculate the Payment for each employee and the total payment for all employees.
The formula will be in the E5 cell.

=IF(WEEKDAY(C5, 2)>5, D5*$H$6,D5*$H$7)

Then use the SUM function for aggregating the individual payment.

Calculating Payment Including Weekends

Read More: How to Use DAYS Function in Excel (7 Examples)


Excel WORKDAY Function for Holidays

To determine the work date that will occur after a certain number of days from the start date, utilize Excel’s WORKDAY function, which is a DATE function.
Additionally, this function provides an optional holiday parameter that, if omitted, automatically counts weekends, including Saturdays and Sundays, as holidays and determines the future work date after a specified number of days.
The syntax of the WORKDAY function is.

=WORKDAY(Start_date,Days,Holidays)

Suppose, we have the following dataset which has column headers as Starting Date, Production Days, and Completion Days. It has also columns of Holiday and their corresponding Date. In the Starting Date column, there are dates of project starts, in Column C there is the estimated time needed to complete those projects.
We need to find out the Completion Date in Column D.

Excel WORKDAY Function Holidays

  • Firstly, write the formula in the D5 cell like this.
=WORKDAY(B5,C5,G5:G9)

Excel WEEKDAY Function

  • Eventually, press ENTER
  • Lastly, use the Fill Handle
  • Consequently, you’ll get the outputs of project Completion Dates.

Read More: How to Use WORKDAY.INTL Function in Excel (A Complete Guideline)


Common Errors While Using WEEKDAY Function

You might see a couple of errors while using the WEEKDAY function. Let’s have a look.

  • #NUM – if the serial number is out of range for the current date’s base value
    – if the return_type is out of range from the return_type value table as shown in the arguments section.
  • #VALUE! – occurs when either the given serial_number or the given [return_type] is non-numeric.

So be careful while handling the function. Hope you can understand and fix if you see these errors. Also, we can help if you need.


Conclusion

This is how you can apply the WEEKDAY function to get the day of the week (weekday). Additionally, you have the opportunity to combine the function with other Excel functions. If you have an interesting and unique method of using the WEEKDAY function, please share it in the comments section below. Thanks for being with us.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo