Networkdays - using holidays from more than one range of cells - HELP

[98aallen]

Hi,

I am currently using the networkdays formula to return the amount of working days remaining in each month
and also using data from a list of bank holidays to exclude all bank holidays too, the problem is i also want to exclude any annual leave but this is stored in a different range of cells, how would i do this?

the formula i am currently using is

=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),U4:U22)

and the other range of cells i want to also use the dates from to exclude dates from is U25:U54

Hope someone can help, would really appreciate it

Thanks in advanced

 

[Joe4]

What exactly is in cells U23:U24?
If it is not any valid dates, try using: U4:U54 and see if that works.
As long as U23:U24 does not include any dates that you do not want to exclude, I think it may be OK.

 

[98aallen]

Hi,

U23:U24 one cell is just blank, and the other contains text saying "date"

there are basically two small charts, on with the bank holdays, and on underneath containing annual leave dates, each chart has one cell as a header.

Doing U4:U54 doesnt work, it returns #VAULE in the cell then

 

[Joe4]

I did some testing, and was able to successfully create a named range that is actually two distinct ranges, like you have.
However, the NETWORKDAYS function did not like that either, and I got the same #VALUE error.

Looks like you are going to need to make it one continuous range in order to get it to work.
If you cannot edit that range, maybe use some unused portion of your sheets, and set references like this:

=U4
=U5
...
=U22
=U25
=U26
...
=U54

and then use this new range in your formula.

By the way, I did some other testing, and it doesn't mind any blank cells in your range. It just doesn't like text entries in your range.

 

[98aallen]

Hi, Thanks

I really appreciate your help, yeah i noticed it is okay with blank cells, i will re-design it slightly then, im sure i can figure something out, just hurts my OCD lol

Thanks for your help

 

[Joe4]

You are welcome. I was kind of surprised that the Named Range solution didn't work. I guess it just doesn't like non-continuous range. Maybe it is because it needs to "scan" the range area.

 

[barry houdini]

If you use IFERROR like this you can keep one range, even if text values are included

=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),IFERROR(U4:U54+0,0))

but that needs to be "array entered" with CTRL+SHIFT+ENTER

 

[98aallen]

If you use IFERROR like this you can keep one range, even if text values are included

=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),IFERROR(U4:U54+0,0))

but that needs to be "array entered" with CTRL+SHIFT+ENTER

Hi,

Thanks for your help, i tried that but it returned a message along the lines of "array formula can not be used in merged cells"

no worries anyway, i done as was suggested above and set cell references and then applied that new range in the formula, and then just made the new cell references white text so they cant be seen on the worksheet, works a treat