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. If you need more flexibility, use the NETWORKDAYS.INTL function.
Syntax
The syntax for the NETWORKDAYS
function in Microsoft Excel is:
=NETWORKDAYS( start_date, end_date, [holidays]
)
Parameters or Arguments
start_date
The start date to use in the
calculation and should be entered as a serial date, not a text date.
end_date
The end date to use in the
calculation and should be entered as a serial date, not a text date.
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: F2:F5) or as a list of
serial numbers that represent the holiday dates.
Networkdays Function Examples
The spreadsheet below provides
two examples of the Excel Networkdays function, used to calculate the number of
work days between the two dates Dec 01, 2015 and Jan 15, 2016.
One of the examples (in cell
A8) ignores holidays (other than weekends), while the second example (in cell
A9) provides a list of holidays to be excluded from the day count in the
calculation.
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(
B1, B2 )
|
- No
Holidays
|
|
9
|
=NETWORKDAYS(
B1, B2, B3:B5 )
|
- Excludes
holidays in cells B3-B5
|
|
Results:
A
|
B
|
C
|
|
7
|
Work
days between Dec 1st, 2015 & Jan 15th, 2016:
|
||
8
|
34
|
- No
Holidays
|
|
9
|
31
|
- Excludes
holidays in cells B3-B5
|
|
In the above spreadsheet:
► 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
the example in cell A9, the [holidays] array (in cells B3 - B5) is provided to
the Networkdays function. Therefore the calculation excludes Saturdays and
Sundays and the listed Christmas and New Year holidays.
References
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501
No comments:
Post a Comment