Can Net.workdays.intl spill(#)?

[Geir Hogstad]

Hi,

Can net workdays intl spill, if start date and enda date is from dynamic arrays, like filter function.

=NETT.WorkdaysINTL(F8#;G8#;1;Datalister!$C$6:$C$17)

Result:

Result #VALUE

Best Regards

- Geir

[#2]

@Geir Hogstad 

It works if you first convert the range to array, e.g. by applying some dummy arithmetic operation. This and some other functions don't do that automatically. For example

[#3]

Thank you @Sergei Baklan I found another solution, based on your input.

I Used INDIRECT to build the refference for the last row in the dynamic table.

NETT.WORKDAYS.INTL(+F8:INDIRERECT("F"&ROWS(F8#)-1+8);+G8:INDIRECT("G"&ROWS(F8#)-1+8);1;Datalister!$C$6:$C$17))

ROWS# calculate number of rows in the dynamic array

+8 because the first row is 8

-1 to adjust for the first row. 

If its two rows with data, the result will be 2-1+8, the refference will be F9

Best Regards

- Geir

[#4 · Accepted solution]

@Geir Hogstad 

Geir, I'd recommend to avoid INDIRECT() as much as possible. Two reasons

1) It affects performance which could be not important for relatively small data set

2) It's not suitable from maintenance point of view. If, for example, one  day you decide to insert another column between F and G, or move the range to the left/right, you shall manually change "G" within INDIRECT() on another column.

Variant could be, if you have the spill in F8,

=NETWORKDAYS.INTL(+F8#,+G8:INDEX(G8:G100,ROWS(F8#)))

as

[#5]

Thank you @Sergei Baklan it worked perfect.

Best Regards

- Geir

[#6]

@Geir Hogstad , glad to help