Networkdays by state whilst excluding each states public holiday andnational holidays

[Mitchell Miles]

Hi,

I have created a formula that has calculated the amount of net workdays between specified dates by using a lot of "IF" in the formula to work out each states holidays by using below formula,

=NETWORKDAYS(E2,B2,IF(I2='HOLIDAY lookup'!$A$1,'HOLIDAY lookup'!$A$2:$A$12,IF(I2='HOLIDAY lookup'!$B$1,'HOLIDAY lookup'!$B$2:$B$12,IF(I2='HOLIDAY lookup'!$C$1,'HOLIDAY lookup'!$C$2:$C$13,IF(I2='HOLIDAY lookup'!$D$1,'HOLIDAY lookup'!$D$2:$D$13,IF(I2='HOLIDAY lookup'!$E$1,'HOLIDAY lookup'!$E$2:$E$12,IF(I2='HOLIDAY lookup'!$F$1,'HOLIDAY lookup'!$F$2:$F$12,IF(I2='HOLIDAY lookup'!$G$1,'HOLIDAY lookup'!$G$2:$G$21))))))))-1

I have to continue this trend across to column N but I get an error message of too much nesting. Please help.

 

[Puppet_Sock]

Hi,

I have created a formula that has calculated the amount of net workdays between specified dates by using a lot of "IF" in the formula to work out each states holidays by using below formula,

 =NETWORKDAYS(E2,B2,IF(I2='HOLIDAY lookup'!$A$1,'HOLIDAY lookup'!$A$2:$A$12,IF(I2='HOLIDAY lookup'!$B$1,'HOLIDAY lookup'!$B$2:$B$12,IF(I2='HOLIDAY lookup'!$C$1,'HOLIDAY lookup'!$C$2:$C$13,IF(I2='HOLIDAY lookup'!$D$1,'HOLIDAY lookup'!$D$2:$D$13,IF(I2='HOLIDAY lookup'!$E$1,'HOLIDAY lookup'!$E$2:$E$12,IF(I2='HOLIDAY lookup'!$F$1,'HOLIDAY lookup'!$F$2:$F$12,IF(I2='HOLIDAY lookup'!$G$1,'HOLIDAY lookup'!$G$2:$G$21))))))))-1

I have to continue this trend across to column N but I get an error message of too much nesting. Please help.

Can you insert a column or two someplace with part of that formula
evaluated?
Something like an "Is Holiday" column or something?

Or, can you make a new worksheet that has the holidays listed in some
simplified way so you can accomplish this with a single lookup?

Or, can you create a VB function that does this calculation for you?
Socks