"Empty" values in holiday parameter for NETWORKDAYS() function

[Guest]

If the holiday vector in NETWORKDAYS(start,end,holiday_vector) contains any
"null" strings (i.e. they've had the value "" inserted), then NETWORKDAYS()
returns #VALUE!

Any ideas how to get around this? I do need to have the empty cells - the
vector can be used for different countries, with different numbers of
holidays. And I can't use zero as the empty filler, since 0 actually
represents a valid date.

I'm converting from OpenOffice's spreadsheet which doesn't have the same
problem.

tia.
rmtp

 

[Debra Dalgleish]

You could use a dynamic range for the holidays, and it will adjust to
fit the number of dates. There are instructions here:

http://www.contextures.com/xlNames01.html

 

[Guest]

Try this:

Instead of "", try using a common holiday (even if it would be a duplicate),
like 1/1 or 12/25 or whatever would be most common to all situations.

Alternatively, if your list is populated by formulas....have the skipped
cell just refer to the date above it.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Thanks, that's the workround I decided to use while waiting for a reply
The one you reference is more elegant than mine though:

NETWORKDAYS(start,end,$N$6:INDIRECT(CONCATENATE("$N$",ROW($N$6)+$N$21-1)))

where my holiday vector (including blanks) is in N6:N20, and N21 contains
COUNT(N6:N20)

rmtp

 

[Guest]

Alternatively, if your list is populated by formulas....have the skipped

cell just refer to the date above it.

Thanks. Yes, that would work. I considered that - merely duplicating the
final holiday into the remaining formerly-blank cells. A bit clumsy though,
in terms of how that holiday vector looks.

rmtp

 

[Biff]

Hi!

Here's another option:

A1 = start date
A2 = end date

J1:J10 = holidays. Some cells may contain "".

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=NETWORKDAYS(A1,A2,IF(ISNUMBER(J1:J10),J1:J10,1000000))

The blank ("") cells in the holiday array will evaluate to serial date
1000000 = 11/26/4637. I doubt that you're calculating work days that far
into the future!

Biff

 

[daddylonglegs]

Thanks, that's the workround I decided to use while waiting for a repl

The one you reference is more elegant than mine though:

NETWORKDAYS(start,end,$N$6:INDIRECT(CONCATENATE("$N$",ROW($N$6)+$N$21-1)))

where my holiday vector (including blanks) is in N6:N20, and N2
contains
COUNT(N6:N20)

Perhaps better....

=NETWORKDAYS(start,end,$N$6:INDEX($N$6:$N$20,$N$21)