WORKDAY Function

[Connie Martin]

I've read, read, and read about the WORKDAY function (Excel Help and online)
and cannot find a solution as to why my formula is giving me #NUM!. I read
the reasons why, but cannot find any errors. All columns with dates are
formated to 'date'. Analysis Toolpak is checked in Tools/Add-ins. The
formula in I3 is this: =WORKDAY(J3,1,NWD)
• J3 is the date 16-Feb-10, formatted as date (format cells, etc.)
• J3's date comes from a VLOOKUP function, from a column that is formatted
as a date
• NWD (meaning "non-workdays") is a named range in another worksheet of the
same workbook, and is formatted as a date (format cells, etc.).
• NWD was inserted into the formula by Insert/Name/Paste
• With this formula in I3 it should give 17-Feb-10. Very simple, this one.
But it gives me #NUM!.
Can anyone shed some light on this? It's getting very frustrating! I would
be so very, very grateful if you could. Thank you. Connie

 

[Luke M]

Agreed, very confusing. From my tests, is appears WORKDAY is able to handle a
start date formatted as text, so that can't be the problem. You clearly have
an legitimate number in the days arguement...

The only thing I was able to do that created a #NUM error in your scenario
was to have a negative number in the NWD range. This is a slim chance, but
possibly?

Further debugging:
Does
=WORKDAY(J3,1)
work?

Instead of using the named range, manually input an address, starting with a
smaller size, and gradually increasing until the error is generated.

Sorry I wasn't able to find a easy solution.

 

[Connie Martin]

Luke, thank you so much for replying. Your debug test worked. So the
problem was in the named range. I did the next thing you said, and put in
the range A1:A10 in the formula instead of NWD, the named range, and formula
worked! So, I looked at the named range, as it had to be the problem. When
I named the range, I selected the entire column instead of just the cells
with dates. So, I deleted the named range, selected just the dates A1:A10
and renamed it NWD, and the formula works! Marvelous! Oh! I'm so happy!
Now I can go places!! Thank you a million times! Connie

 

[Luke M]

Hurrah, glad it helped.

Furhter study:
I thought at first the problem with having the whole column called out was
all the blank cells, but it's not. It simply can't handle calling out the
entire column. Ironically, if you try this:
=WORKDAY(A1,1,B1:B65535)
you're ok, but if you try to include that last cell:
=WORKDAY(A1,1,B1:B65536)
XL will "help" you out can change the final address to simply B:B, and then
crash the formula!