Networkdays Function

Purpose

Return value

Syntax

=NETWORKDAYS(start_date,end_date,[holidays])

• start_date - The start date.
• end_date - The end date.
• holidays - [optional] A list of non-work days as dates.

Using the NETWORKDAYS function

The NETWORKDAYS function returns the number of working days between two dates, automatically excluding weekends (Saturday and Sunday) and optionally excluding holidays provided as a list of dates. NETWORKDAYS can be used to calculate employee benefits that accrue based on days worked, the number of working days available during a project, the number of working days required to resolve a customer support issue, etc.

NETWORKDAYS takes three arguments : start_date , end_date , and holidays . All three arguments must be valid Excel dates. Holidays are optional. To exclude holidays, provide a range of valid Excel dates for the holidays argument. Holidays are treated as non-working days and will not be included in the result.

NETWORKDAYS includes both the start date and end date when calculating workdays. If you give NETWORKDAYS the same date for start date and end date, and the date is not a weekend or holiday, it will return 1.

Example

The general form of a NETWORKDAYS formula is as follows:

=NETWORKDAYS(start,end) // exclude weekends
=NETWORKDAYS(start,end,holidays) // exclude weekends + holidays

In the example shown, holidays is the named range H5:H13, which contains non-working days in 2021. Columns E and F show the number of working days in each month of the year. The formula in cell E5 (Result 1) contains the NETWORKDAYS function but does not take into account holidays:

=NETWORKDAYS(B5,C5) // returns 21

The formula in cell F5 (Result 2) does take into account holidays:

=NETWORKDAYS(B5,C5,holidays) // returns 19

NETWORKDAYS will automatically exclude both Saturday and Sunday. This behavior is not configurable. If you need more flexibility, the NETWORKDAYS.INTL function provides a way to treat any day of the week as a non-working day.

Notes

• NETWORKDAYS calculates whole workdays, ignoring any time values.
• NETWORKDAYS will automatically exclude both Saturday and Sunday.
• NETWORKDAYS includes both the start date and end date when calculating workdays.
• To create a custom weekend schedule, see the NETWORKDAYS.INTL function.

Purpose

Return value

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.

Using the 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.