Calculating NETWORKDAYS considering leaves and holidays for each employee

[dpkdileep]

Hi,

I have some data where the networkdays to be calculated for each activity.
Also, the leaves of each person and the common holidays are to be considered in the calculation.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]Common Holiday[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3-Sep-19[/TD]
[TD]19-Sep-19[/TD]
[TD]16-Sep-19[/TD]
[TD]9-Sep-19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13-Sep-19[/TD]
[TD]20-Sep-19[/TD]
[TD]20-Sep-19[/TD]
[TD]11-Sep-19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14-Sep-19[/TD]
[TD]23-Sep-19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Col F[/TD]
[TD]Col G[/TD]
[TD]Col H[/TD]
[TD]Col I[/TD]
[TD]Col J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Activity Name[/TD]
[TD]PoC Name[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Total Workdays[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]XX[/TD]
[TD]A[/TD]
[TD]2-Sep-19[/TD]
[TD]5-Sep-19[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]YY[/TD]
[TD]B[/TD]
[TD]6-Sep-19[/TD]
[TD]10-Sep-19[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ZZ[/TD]
[TD]C[/TD]
[TD]12-Sep-19[/TD]
[TD]17-Sep-19[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AA[/TD]
[TD]B[/TD]
[TD]2-Sep-19[/TD]
[TD]5-Sep-19[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]BB[/TD]
[TD]C[/TD]
[TD]6-Sep-19[/TD]
[TD]10-Sep-19[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]CC[/TD]
[TD]A[/TD]
[TD]12-Sep-19[/TD]
[TD]17-Sep-19[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]



















I am able to get the results separately.
For leaves: Nested IF function for each person.
For Common holidays: Normal "Holidays" option available within NETWORKDAYS function in excel.

I have used the following formula for the result in column J:
=IF(G2="A",NETWORKDAYS(H2,I2,$A$2:$A$4),IF(G2="B",NETWORKDAYS(H2,I2,$B$2:$B$6),IF(G2="C",NETWORKDAYS(H2,I2,$C$2:$C$6))))


Can someone help me to integrate leaves and common holidays into the calculation in a simple manner.
I do not wish to use nested IF as the real data contains more than 50 personnel.

Thanks in advance.

 

[jasonb75]

This should do it

=NETWORKDAYS(H2,I2,CHOOSE({1,2},INDEX($A$2:$C$4,0,MATCH(G2,$A$1:$C$1,0)),$D$2:$D$4))

 

[dpkdileep]

Thanks for the reply. Apologies from my side for not saying this at the time of answer.