2 Sets of holiday ranges NETWORKDAYS

[All2Cheesy]

Hi All,

Is it possible to set two sets of holiday ranges when writing a network days function?
Below is my current formula:

=IFERROR(NETWORKDAYS(C2,M2,LARGE((W2,X2),ROW(INDIRECT("1:"&ROWS(W2)+ROWS(X2)))))-1,"")

W2 and X2 refer to cell ranges.

Assistance would be greatly appreciated. Thank you.

 

[All2Cheesy]

Awesome, thanks for that.

I'll give that a go the next time I get a bit of time. (Some new projects at work have come up).
For now I've just set a temporary fix by highlighting any holiday dates via conditional formatting.

 

[Domenic]

You haven't answered Aladin's question, but it looks like W2 and X2 refer to two different ranges of cells. If so, your original formula can be changed to the following...

=IF(COUNT(W2,X2)>0,NETWORKDAYS(C2,M2,LARGE((W2,X2),ROW(INDIRECT("1:"&COUNT(W2)+COUNT(X2))))),NETWORKDAYS(C2,M2))

...confirmed with CONTROL+SHIFT+ENTER.

 

[MarcelBeug]

You haven't answered Aladin's question, but it looks like W2 and X2 refer to two different ranges of cells. If so, your original formula can be changed to the following...

=IF(COUNT(W2,X2)>0,NETWORKDAYS(C2,M2,LARGE((W2,X2),ROW(INDIRECT("1:"&COUNT(W2)+COUNT(X2))))),NETWORKDAYS(C2,M2))

...confirmed with CONTROL+SHIFT+ENTER.

Interesting concatenation of arrays.

However, it looks like you omitted a bunch of INDIRECTs.
My suggestion would be to define names W_2 as =INDIRECT(W2) and X_2 as =INDIRECT(X2), and adjust your array formula to:

=IF(COUNT(W_2,X_2)>0,NETWORKDAYS(C2,M2,LARGE((W_2,X_2),ROW(INDIRECT("1:"&COUNT(W_2)+COUNT(X_2))))),NETWORKDAYS(C2,M2))

 

[Domenic]

Hi Marcel,

Actually, I thought those were named ranges. But it looks like those two cells, W2 and X2, likely contain strings that refer to ranges, since one cannot have a cell address as a name for a range. If those two cells do contain strings that refer to ranges, then yeah, INDIRECT would be needed, as you've already indicated.

Thanks for pointing that out!

Cheers!