Summary

The Excel NETWORKDAYS.INTL function calculates the number of working days between two dates. NETWORKDAYS.INTL can optionally exclude a list of holidays and provides a way to specify which days of the week are considered weekends.

Purpose

Get work days between two dates

Return value

A number representing days.

Syntax

``=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])``
• start_date - The start date.
• end_date - The end date.
• weekend - [optional] Setting for which days of the week should be considered weekends.
• holidays - [optional] A reference to dates that should be considered non-work days.

How to use

The NETWORKDAYS.INTL function returns the number of working days between two dates, taking into account holidays and weekends. This function is more robust than the NETWORKDAYS function because it allows you to control which days of the week are considered weekends.

NETWORKDAYS.INTL takes four arguments: start_date, end_date, weekend, and holidays. The start_date, end_date and holidays arguments must be valid Excel dates. The weekend argument controls which days of the week are considered weekends, and therefore not included in the count. Holidays are also treated as non-working days and will not be included in the result.

Both the weekend and holidays arguments are optional. By default, NETWORKDAYS.INTL will exclude Saturdays and Sundays, but this can be customized as explained below. To exclude holidays, supply a range that contains non-working dates for the holiday argument.

NETWORKDAYS.INTL includes both the start date and end date when calculating workdays – if you give NETWORKDAYS.INTL the same date for start_date and end_date, and the date is not a weekend or holiday, the result is 1.

Examples

In the example shown, the following formulas are used:

``````=NETWORKDAYS.INTL(B5,C5) // result 1, default
=NETWORKDAYS.INTL(B5,C5,1,holidays) // result 2, exclude holidays
=NETWORKDAYS.INTL(B5,C5,"1000000") // result 3, Monday is weekend
``````

where "holidays" is the named range I5:I13.

Result 1 in column E shows the default configuration, where Saturday and Sunday are treated as weekends and excluded from the count. Result 2 in column D shows the effect of excluding holidays from the working day count. Result 3 shows how the NETWORKDAYS.INTL function can be configured to define custom weekends. The text string "1000000" sets Mondays as a weekend, and all other days are considered working days. See below for more detail on configuring weekends.

Configuring weekends

The NETWORKDAYS.INTL function provides two options to configure weekends. The first option is to supply a number as shown in the table below.

 Weekend number Weekend days 1 (default) Saturday, Sunday 2 Sunday, Monday 3 Monday, Tuesday 4 Tuesday, Wednesday 5 Wednesday, Thursday 6 Thursday, Friday 7 Friday, Saturday 11 Sunday only 12 Monday only 13 Tuesday only 14 Wednesday only 15 Thursday only 16 Friday only 17 Saturday only

The second way to configure weekends is to provide a text string composed of 1s and 0s. This text is provided as a string of 7 characters which must be either 1 or 0. In this scheme, the number 1 means weekend and 0 means workday. Each character represents a different day of the week, starting with the first character as Monday. Below are some examples:

``````NETWORKDAYS.INTL(start,end,"0101011") // workdays = M,W,F
NETWORKDAYS.INTL(start,end,"1010111") // workdays = Tue, Thu
NETWORKDAYS.INTL(start,end,"1111100") // workdays = Sat,Sun
NETWORKDAYS.INTL(start,end,"0000000") // all workdays, no weekends
``````

Notes:

• If start_date is greater than end_date, the function returns a negative value.
• NETWORKDAYS.INTL includes both the start date and end date when calculating workdays. This means if you give NETWORKDAYS.INTL the same date for start date and end date, it will return 1.
• If start_date or end_date are out of range, NETWORKDAYS.INTL returns the #NUM! error.
• If weekend is invalid, NETWORKDAYS.INTL returns the #VALUE! error.

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.