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.

 

[Aladin Akyurek]

Not very clear. Explanations/descriptions with non-working formulas do often thwart understanding, rather than help. That said...

Is this:

=NETWORKDAYS(C2,M2,ROW(INDIRECT("1:"&MAX(W2:X2)))

what you are after?

 

[All2Cheesy]

Ah right, sorry about that.

You've got the right idea behind what I'm after, however, I'm still not achieving the correct result. I believe this may be because cells W2 and X2 seem to be referencing an individual cell, rather than the selected range.

 

[Domenic]

Your formula seems fine. Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

 

[All2Cheesy]

I did.

I believe the error is being caused due to W2 and X2 only equalling the first value of the selected range, rather than the entire range. I'm not too sure how to get around this however.

 

[Aladin Akyurek]

What do you have in W2 and X2? Are these named ranges or just two cells?

 

[MarcelBeug]

Try:

=NETWORKDAYS(C2,M2,HDays)

With defined names:

HDays=MMULT(IF(ISNUMBER(HDays_1),HDays_1,0),{1\0})+MMULT(IF(ISNUMBER(HDays_2),HDays_2,0),{0\1})
HDays_1=OFFSET(INDIRECT(Sheet1!$W$2),0,0,MaxRows)
HDays_2=OFFSET(INDIRECT(Sheet1!$X$2),0,0,MaxRows)
MaxRows=MAX(ROWS(INDIRECT(Sheet1!$W$2)),ROWS(INDIRECT(Sheet1!$X$2)))

Assuming:
- your data is in Sheet1
- your holiday ranges are in separate columns (e.g. S1:S8 and T1:T6)
- no (numeric) data in the same column below the holidays (e.g. T7:T8 must be empty or text)

Basically a matrix HDays is built with the number of rows equal to the largest range of holidays (MaxRows) and 2 columns, the first column populated with the holidays from range1 (0 for missing values) and the second column with the holidays from range 2 (0 for missing values, in the example for T7 and T8).

 

[All2Cheesy]

I'm having a bit of trouble with
HDays=MMULT(IF(ISNUMBER(HDays_1),HDays_1,0),{1\0})+MMULT(IF(ISNUMBER(HDays_2),HDays_2,0),{0\1})

Can I get a bit of a run through of it?

 

[Aladin Akyurek]

Ah right, sorry about that.

You've got the right idea behind what I'm after, however, I'm still not achieving the correct result. I believe this may be because cells W2 and X2 seem to be referencing an individual cell, rather than the selected range.

Still don't know what you have in W2 and in X2. Names that stand for ranges, the so-called named ranges?

If they are named ranges for different sets of holiday dates, what is the condition for choosing one above another or must both be used?

 

[MarcelBeug]

I'm having a bit of trouble with
HDays=MMULT(IF(ISNUMBER(HDays_1),HDays_1,0),{1\0})+MMULT(IF(ISNUMBER(HDays_2),HDays_2,0),{0\1})

Can I get a bit of a run through of it?

Sure. first of all I hope it is working with you and your trouble is just about understanding the formula.
In case it doesn't work with you, you might need to adjust the delimiter \ to , or ; or | (I'm not sure: it depends on international settings).

As already explained: Basically a matrix HDays is built with the number of rows equal to the largest range of holidays (MaxRows) and 2 columns, the first column populated with the holidays from range1 (0 for missing values) and the second column with the holidays from range 2 (0 for missing values, in the example for T7 and T8).

Example if HDays_1 would be values in D2:D4 40,000 40,500, 41,000 and HDays_2 in F7:F9 42,222, 42,333 and blank and let's say we have in H1:J1 values 1, 0, 1 (instead of the hardcoded {1\0} and {0\1})
then MMULT(IF(ISNUMBER(HDays_1),HDays_1,0),H1:I1) will result in a 3x2 matrix with 40,000, 40,500 and 41,000 in the first column and zeroes in the second column.
The part MMULT(IF(ISNUMBER(HDays_2),HDays_2,0),I1:J1) will result in a 3x2 matrix with zeroes in the first column and 42,222, 42,333 and 0 (replacing the blank in F9) in the second column.
So the sum of these 2 parts will return a matrix with the first range of holidays in column 1 and the second range of holidays in column 2.

HDays_1 and HDays_2 are the original holiday ranges with cells added to the smallest range so both are equally sized.