Need help with formula

[Connie Martin]

I am trying to adapt a formula in I2 from another spreadsheet that works
well, but won't in mine. I've traced the error, but I would need help to
understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be
advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a
list of non-workdays, and defined the column of dates with the name "NWD".
What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb.
5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb.
5, and Feb. 6 and 7 are non-workdays in NWD, then it would give Feb. 8. I
don't know how this formula works in the other spreadsheet, but it does.
Can anyone help? Connie

 

[Luke M]

The first part of formula looks fishy. You ask it to check if the cell equals
the text string "0-Jan-00". I think what's actually happening is the cell is
formatted as a date, and it has a value of 0. In which case, the cell
actually has a value, not text. (as an example, try typing in a cell
(=J2="0-Jan-00"). You'll see that it says "FALSE").

Try this:
=IF(J2=0,"To be advised",WORKDAY(J2,1,NWD))

 

[Connie Martin]

Hi Luke,
I tried the first thing you mentioned and it gives #VALUE!. Then I tried
the formula you gave and it still gives #NUM!. I tried then by leaving the
IF statement out and putting simply =WORKDAY(J2,1,NWD) and it still gives
#NUM!. Connie

 

[Eduardo]

Hi,
try

=IF(or(J2=0,J2=""),"To be advised",WORKDAY(J2,1,NWD))

 

[Luke M]

Odd. The #NUM error appears if the Start_Date + number of days yields an
invalid date. If J2 was not a valid date, you would get the #VALUE error, so
that's not the problem...What exactly is in J2? If it's a formula, please
list that. Is it possible it's a negative number? If so, we could compensate
with:

=IF(J2<=0,"To be advised",WORKDAY(J2,1,NWD))

 

[alanglloyd]

Hi Luke,
I tried the first thing you mentioned and it gives #VALUE!. �ThenI tried
the formula you gave and it still gives #NUM!. �I tried then by leaving the
IF statement out and putting simply =WORKDAY(J2,1,NWD) and it still gives
#NUM!. �Connie

You may have more than one possible error in your function Tie it down
and make sure you understand the WORKDAY() function as follows.

1 On a blank woeksheet enter 40194 & 40195 in two adjacent cells
this is saturday & sunday Jan 16 & 17 2010

2 In a cell enter =WORKDAY(40194,1,40195)
this cell should return 40196 ie monday Jan 18 this shows the function
works

3 in the above cell in 2 enter the same formula but insread of 40195
enter the two cells of 1 by selecting 40195 in the function, then
dragging over the two cells in 1.
You should get 40196. This shows the function works with a non-working
day array

4 in the above cell in 2 select the 40194 in the function and click on
the cell in 1 with 40194 in it.
You should get the same result of 40196. This shows the function works
with a reference to the starting date.

5 Now select the two cells in 1 and name them NWD, Then enter NWD as
the third parameter in the formula in above cell in 2. This checks you
can use a named cell in the function.

In among the above you should find what you are doing wrong in your
real worksheet.

If you format the display of the cels in 1 as dd-mm-yyyy you should
see the values as dates.

Good hunting

Alan Lloyd

 

[Connie Martin]

There is a formula in J2. It is: =VLOOKUP(B3,NIRAV,10,0). NIRAV is another
defined name worksheet.
The formula you gave here now gives me #VALUE!.
Connie

 

[Connie Martin]

That gives me #VALUE!. Does it have something to do with the date in J2
which is from a formula, which is: =VLOOKUP(B3,NIRAV,10,0)? The thing is,
the worksheet where this is all working perfectly is working from cells with
much more complicated formulas than this one. Connie

 

[Connie Martin]

Everything worked fine in the steps you outlined. My worksheet does not.
I'm still sitting with the error code. It's exactly as it is in the other
spreadsheet and it works fine. Won't in mine. Connie