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

Excel WEEKDAY Function: Syntax & Arguments

Syntax

Return Values

0 to 7 (in numbers)

Arguments

Note:

1. Dates are stored in Microsoft Excel as consecutive serial numbers, which allows them to be used in calculations. Dates should be inserted with 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 do not specify a return_type, the WEEKDAY function returns 1 for Sunday and 7 for Saturday by default. Return type 11-17 is introduced in the Excel 2010 version.

Example 1 – Basic Examples of WEEKDAY Function

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

Here, C5 is the Joining Date of Robert.

• Press ENTER.
• Use the Fill Handle by dragging down the cursor while holding it at the right-bottom corner of the D5 cell like this.

• The outputs will look 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:

• When the value of return_type is 16:

Example 2 – Using WEEKDAY Function with DATE Function

If joining date is given in serial number, you have to use the DATE where the YEAR, MONTH, and DAY functions to return the year, month, and day of the given date respectively.

• Enter the following formula in D5

• Press ENTER.
• Use the Fill Handle.

• The following output is returned.

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.

• Enter the below formula in the D5 cell.

• Hit ENTER and then use the Fill Handle to return the results.

Example 4 – WEEKDAY Combined with CHOOSE Function

• Enter the following formula in the D5 cell and then use the Fill Handle to get all the outputs.

Example 5 – Combined with SWITCH Function

• Enter the below formula in the D5 cell.

Example 6 – WEEKDAY Function to Get Weekdays and Weekends

Apply the following formula in cell D5.

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:

• Select the cells B5:D12.
• Go to Home > choose Conditional Formatting > select New Rule.

• A New Formatting Rule window will appear.
• Choose Use a formula to determine which cells to format.
• Enter the below formula in the formula box.

• Go to Format to select the color of the output.

• Click OK.

• In the New Formatting Rule, click OK.

• Go to New Formatting Rule after selecting the cells. Enter the following formula.

• Go to Format.

• Select any color in the Fill option, here the light blue color is selected.
• Click OK.

• Click OK in the New Formatting Rule

• The outputs will look like this.

Example 8 – Calculating Payment Including Weekends

In the sample dataset the number of working hours is given and you need to calculate the Payment for each employee and the total payment for all employees.
The formula will be entered in the E5 cell.

Use the SUM function for aggregating the individual payment.

Excel WORKDAY Function for Holidays

Excel’s WORKDAY function can be used to determine the work date that will occur a certain number of days after the start date.

The syntax of the WORKDAY function is.

The sample dataset has column headers for Starting Date, Production Days, and Completion Days.

It also has columns for Holidays and their corresponding Date.

In the Starting Date column are project start dates, in Column C there is the estimated time needed to complete those projects.

We need to return the Completion Date in Column D.

• Enter the following formula in the D5 cell.

• Press ENTER
• Use the Fill Handle.
• The outputs of project Completion Dates are returned.

Common Errors While Using WEEKDAY Function

• #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.

Download Excel Workbook

<< Go Back to Excel Functions | Learn Excel