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

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

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

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

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 he

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

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

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,1000 000))

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" wrote in message
...
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 Wrote:
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 ))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=537505