How to exclude weekends in Excel formula

Syntax

WORKDAY(start_date, days, [holidays])

The WORKDAY function syntax has the following arguments:

• Start_date    Required. A date that represents the start date.

• Days    Required. The number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

• Holidays    Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

• If any argument is not a valid date, WORKDAY returns the #VALUE! error value.

• If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.

• If days is not an integer, it is truncated.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Table of Contents hide

Download Practice Workbook

2 Effective Ways to Calculate Working Days in Excel Excluding Weekends and Holidays

1. Using NETWORKDAYS Function

1.1 Excluding Only Weekends

1.2 Excluding Both Weekends and Holidays

2. Applying NETWORKDAYS.INTL Function

2.1 Excluding Only Weekends

2.2 Excluding Both Weekends and Holidays

Conclusion

Download Practice Workbook

Calculate Working Days Excluding Weekends and Holidays.xlsx

2 Effective Ways to Calculate Working Days in Excel Excluding Weekends and Holidays

In this article, we will discuss 2 handy ways to calculate working days in Excel, excluding weekends and holidays. Firstly, we will use the NETWORKDAYS function to calculate the workdays for two cases, one considering only the weekends and the other considering both weekends and holidays. Then, we will use the NETWORKDAYS.INTL function to calculate workdays for both cases mentioned earlier.

1. Using NETWORKDAYS Function

The NETWORKDAYS function calculates the number of workdays between two dates considering both weekends and holidays. This function assumes that the weekend is on Saturday and Sunday. We will use it to calculate the total number of workdays between two dates, considering weekdays as well as holidays.

1.1 Excluding Only Weekends

In this method, we will use the NETWORKDAYS function and consider only the weekends.

Steps:

• Select the E5 cell and write down the following formula,

=NETWORKDAYS(B5,C5)

• Then, hit Enter.

• Consequently, we will get the net workdays excluding the weekends.
• Then, pull the cursor down to the last data cell to get the values for all the data.

1.2 Excluding Both Weekends and Holidays

In this instance, we will consider weekends and holidays when calculating the net workdays.

Steps:

• To begin with, select the E5 cell and then write the following formula down,

=NETWORKDAYS(B5,C5,$D$13:$D$15)

• In this case, the ($D$13:$D$15) denotes the holidays.
• Then, hit Enter.

• Consequently, we will get the net workdays excluding the weekends as well as the holidays.
• Next, lower the cursor to the last data cell.
• Excel will automatically fill the rest of the cells according to the formula.

2. Applying NETWORKDAYS.INTL Function

In this method, we will count the workdays using the NETWORKDAYS.INTL function. Here, we will consider weekends other than the regular Saturday and Sunday weekends.

2.1 Excluding Only Weekends

In this instance, we will calculate the net workdays excluding only the weekends.

Steps:

• Firstly, choose the E5 cell and write down the following formula,

=NETWORKDAYS.INTL(B5,C5,7)

• Then, hit Enter.

• As a result, we will receive the net workdays excluding weekends.
• Afterward, move the cursor down to the final data cell to obtain the values for all the data.

In this case, the third argument is 7 which denotes a Friday and Saturday weekend. The following is the list of numbers that denote different weekends.

2.2 Excluding Both Weekends and Holidays

In this case, we will use the NETWORKDAYS.INTL function to get the values of total workdays between two dates. In this case, we will keep in mind not only the weekends but also the holidays.

Steps:

• To start with, choose the E5 cell and write down the following formula,

=NETWORKDAYS.INTL(B5,C5,7,$D$13:$D$15)

• Then, hit the Enter button.

• As a result, we will get the total workdays excluding the weekends and holidays.
• Next, move the cursor down to the last data cell.
• The rest of the cells will be automatically filled according to the formula.

Conclusion

This article is about counting the number of working days in Excel excluding the weekends and holidays. As you can see, there are two functions which are used to calculate the working days. The procedure is very simple and doesn’t require too many options.

How do I ignore weekends in Excel?

How do I exclude weekends from two dates?

How do I exclude Sundays in Excel?