NETWORKDAYS.INTL Function

NETWORKDAYS.INTL Function


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.


Syntax

The syntax for the NETWORKDAYS.INTL function in Microsoft Excel is:

=NETWORKDAYS.INTL( start_date, end_date, [weekend], [holidays] )

Parameters or Arguments

start_date

The start date to use in the calculation. It must be entered using the DATE function.

end_date

The end date to use in the calculation. It must be entered using the DATE function.

weekend

Optional. It is the days of the week to include as weekend days. If this parameter is omitted, it will assume that weekends include Saturday and Sunday. The weekend parameter can be one of the following numeric or string values:
Value
Explanation(days of week to include as weekend days)
1
Saturday and Sunday (default)
2
Sunday and Monday
3
Monday and Tuesday
4
Tuesday and Wednesday
5
Wednesday and Thursday
6
Thursday and Friday
7
Friday and Saturday
11
Sunday
12
Monday
13
Tuesday
14
Wednesday
15
Thursday
16
Friday
17
Saturday

holidays

Optional. It is the list of holidays to exclude from the work days calculation. It can be entered either as a range of cells that contain the holiday dates (ie: E2:E4) or as a list of serial numbers that represent the holiday dates.

Networkdays.Intl Function Examples


The spreadsheets below show three examples of the Excel Networkdays.Intl function used to calculate the number of work days between the two dates Dec 01, 2015 and Jan 15, 2016. In each case, the days to be counted as weekends and holidays are different.

Formulas:


A
B
C
1
Start Date:
12/01/2015

2
End Date:
01/15/2016

3
Holidays:
12/25/2015

4

12/28/2015

5

01/01/2016

6



7
Work days between Dec 1st, 2015 & Jan 15th, 2016:
8
=NETWORKDAYS.INTL( B1, B2 )
- Weekends Sat & Sun; No holidays
9
=NETWORKDAYS.INTL( B1, B2, 1, B3:B5 )
- Weekends Sat & Sun; Excludes holidays in cells B3-B5
10
=NETWORKDAYS.INTL( B1, B2, "0000111" )
- Weekends Fri, Sat & Sun; No holidays

Results:


A
B
C
7
Work days between Dec 1st, 2015 & Jan 15th, 2016:
8
34

- Weekends Sat & Sun; No holidays
9
31

- Weekends Sat & Sun; Excludes holidays in cells B3-B5
10
27

- Weekends Fri, Sat & Sun; No holidays

Note that, in the above spreadsheets:

   The [holidays] array has been omitted from the calculation in cell A8. Therefore, this result excludes Saturdays and Sundays but includes all other weekdays, including the holidays at Christmas and New Year.
   In cell A9 of the example spreadsheet, the [weekend] argument is 1 (specifying weekends on Saturdays and Sundays) and the [holidays] array (in cells B3-B5) is provided to the Networkdays.Intl function. Therefore, the calculation excludes Saturdays and Sundays and the listed Christmas and New Year holidays.
   In the example in cell A10, Fridays, Saturdays and Sundays are specified as weekends, but no holiday array has been supplied to the function. Therefore, the calculation excludes Fridays, Saturdays and Sundays but includes all other weekdays, including the holidays at Christmas and New Year.
Note also that, as recommended by Microsoft, in all three calls to the Networkdays.Intl function, the start_date, end_date, and [holidays] arguments have been supplied as cell references.

Comments

Popular posts from this blog

Formatting a Range of Cells In Excel VBA

Trunc() Function

BREAK THE PASSWORD OF THE SHEET IN EXCEL