Excel NETWORKDAYS.INTL Function

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.

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.