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

[RMTP]

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

RMTP wrote:
> 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

[Ron Coderre]

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


"RMTP" wrote:

> 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

[RMTP]

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

"Debra Dalgleish" wrote:

> 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
>
> RMTP wrote:
> > 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
>
>

[RMTP]

> 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

"Ron Coderre" wrote:

> 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
>
>
> "RMTP" wrote:
>
> > 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

[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

"RMTP" <[email protected]> wrote in message
news:[email protected]...
> 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

[daddylonglegs]

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)

Perhaps better....

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