How to use the Excel NETWORKDAYS.INTL function

Summary

The Excel NETWORKDAYS.INTL function calculates the number of working days between two dates. NETWORKDAYS.INTL excludes Saturday and Sunday by default, but provides a way to specify which days of the week are considered weekends. The function can optionally exclude a list of holidays supplied as dates.

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.

Usage notes

NETWORKDAYS.INTL calculates work days between two dates. Work days exclude weekends (Saturday and Sunday by default) and can optionally exclude holidays. This function is more robust that the NETWORKDAYS function, because it allows you to control which days of the week are considered weekends. The weekend argument is set using the codes listed in the table below.

NETWORKDAYS.INTL includes both the start date and end date when calculating work days. This means if you give NETWORKDAYS.INTL the same date for start date and end date, and the date is not a weekend or holiday, it will return 1.

NETWORKDAYS.INTL can also optionally take into account holidays. For the holidays argument, supply a range that contains holiday dates. These are also treated as non-working days and will not be included in the result.

In the example shown, the following formulas are used:

D6=NETWORKDAYS.INTL(B6,C6)
D7=NETWORKDAYS.INTL(B7,C7,17)
D8=NETWORKDAYS.INTL(B8,C8,1,B13:B14)
D9=NETWORKDAYS.INTL(B9,C9,"0000000")
D10=NETWORKDAYS.INTL(B10,C10,"0000000",B13:B14)

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

Mask for workdays

The NETWORKDAYS.INTL function can also accept a "mask" to specify weekends for the weekend argument. The mask is provided as a string of 7 characters which must be either 1 or zero. In this scheme, the number 1 means weekend and 0 means workday. The first digit represents 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 = Fri,Sat
NETWORKDAYS.INTL(start,end,"0000000") // all workdays, no weekends

Notes:

If start_date is greater than end_date, the the function returns a negative value.
NETWORKDAYS.INTL includes both the start date and end date when calculating work days. 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.

Related functions

Excel WORKDAY Function

The Excel WORKDAY function takes a date and returns the nearest working day in the future or past, based on an offset value you provide. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates...

Excel NETWORKDAYS Function

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.

NETWORKDAYS ...