How to Use the NETWORKDAYS Function in Excel

The NETWORKDAYS function in Excel returns the number of working days between two dates, excluding weekends and optionally holidays. This function can be useful for calculating employee benefits, project deadlines, customer service issues, and other scenarios that involve working days.

The syntax of the NETWORKDAYS function is:

= NETWORKDAYS (start_date, end_date, [holidays])

• start_date and end_date are the dates that represent the start and end of the period. They must be valid Excel dates, entered by using the DATE function or as results of other formulas or functions.
• holidays is an optional argument that specifies a range of one or more dates to exclude from the working days. These can be state and federal holidays, floating holidays, or any other non-working days. The dates must also be valid Excel dates.

The NETWORKDAYS function automatically excludes Saturdays and Sundays from the working days, as they are considered weekends. This behavior is not configurable. If you need to customize the weekend days, you can use the NETWORKDAYS.INTL function instead.

The NETWORKDAYS function returns a number that represents the number of whole working days between the start and end dates, inclusive. If the start and end dates are the same, and the date is not a weekend or holiday, the function returns 1. If the start date is after the end date, the function returns a negative number.

Procedures

To use the NETWORKDAYS function in Excel, follow these steps:

1. Enter the start and end dates in two cells, such as A2 and B2. Make sure they are formatted as dates in Excel.
2. Optionally, enter the holidays in a range of cells, such as C2:C10. Make sure they are also formatted as dates in Excel.
3. In another cell, such as D2, enter the formula = NETWORKDAYS (A2,B2,C2:C10). Adjust the cell references as needed.
4. Press Enter to get the result. You can also copy and paste the formula to other cells to calculate the working days for different periods.

Example

To illustrate how the NETWORKDAYS function works, let’s create a scenario where we want to calculate the number of working days in each month of the year 2024, taking into account some holidays.

• In column A, enter the dates for the first day of each month, from January to December. Format them as dates in Excel.
• In column B, enter the dates for the last day of each month, from January to December. Format them as dates in Excel.
• In column C, enter the following holidays for the year 2024: New Year’s Day (1/1/2024), Martin Luther King Jr. Day (1/15/2024), Presidents’ Day (2/19/2024), Memorial Day (5/28/2024), Independence Day (7/4/2024), Labor Day (9/3/2024), Columbus Day (10/8/2024), Veterans Day (11/12/2024), Thanksgiving Day (11/22/2024), and Christmas Day (12/25/2024). Format them as dates in Excel.
• In column D, enter the formula = NETWORKDAYS (A2,B2,C$2:C$11) in cell D2. This will calculate the number of working days in January, excluding the holidays in column C. Note that we use absolute references for the holidays range, so that it does not change when we copy the formula down.
• Copy and paste the formula from D2 to D3:D13 to get the number of working days for the rest of the months.

The result should look something like this:

Other Approaches

As mentioned earlier, the NETWORKDAYS function assumes that weekends are Saturdays and Sundays. If you need to change the weekend days, you can use the NETWORKDAYS.INTL function, which allows you to specify which and how many days are weekend days.

The syntax of the NETWORKDAYS.INTL function is:

= NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays])

• start_date and end_date are the same as in the NETWORKDAYS function.
• weekend is an optional argument that specifies which days are weekend days. You can enter a number from 1 to 7, or a string of seven 0s and 1s, where 1 means a non-working day and 0 means a working day. The numbers and strings correspond to the following weekend schedules:

• holidays is the same as in the NETWORKDAYS function.

For example, if you want to calculate the number of working days between two dates, excluding Fridays and Saturdays as weekend days, you can use the formula:

= NETWORKDAYS.INTL (start_date, end_date, 7, [holidays])

Or, you can use the string “0000110” instead of the number 7.