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.

Row	Col A	Col B	Col C	Col D
1	A	B	C	Common Holiday
2	3-Sep-19	19-Sep-19	16-Sep-19	9-Sep-19
3	13-Sep-19	20-Sep-19	20-Sep-19	11-Sep-19
4	14-Sep-19	23-Sep-19

<tbody>
</tbody>

Row	Col F	Col G	Col H	Col I	Col J
1	Activity Name	PoC Name	Start Date	End Date	Total Workdays
2	XX	A	2-Sep-19	5-Sep-19	3
3	YY	B	6-Sep-19	10-Sep-19	3
4	ZZ	C	12-Sep-19	17-Sep-19	3
5	AA	B	2-Sep-19	5-Sep-19	4
6	BB	C	6-Sep-19	10-Sep-19	3
7	CC	A	12-Sep-19	17-Sep-19	3

<tbody>
</tbody>

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.