Exceljet

Quick, clean, and to the point

Excel NETWORKDAYS.INTL Function

Excel NETWORKDAYS.INTL function
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])
Arguments 
  • 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.
Version 
Usage notes 

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.