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.
References
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501
No comments:
Post a Comment