If statement containing today formula

[Christi]

Hello.

I have a spreadsheet with two sets of information that I am trying to
compare. In columns D thru I, I have the kit information. In columns K thru
L, I have the order information if it is available. I pulled in the order
information with vlkp, copied and paste special text, so there isn't an
embedded formula in the data I am comparing.

The essence of what I am trying to find is the number of days that passed
before the kit was ordered. To do this, my first formula was:
=NETWORKDAYS(E2,L2)-1
E2 being the date the kit was created and L2 being the date the kit was
ordered. This worked well for the kits that had been ordered, but for the
ones that hadn't (order# in K2 is #N/A), I want to subtract it from today's
date that would be refreshed when the spreadsheet openned.

The next formula I tried was:
=IF(K2="#N/A",(NETWORKDAYS(E2,TODAY())-1),(NETWORKDAYS(E2,L2)-1))
This statement works fine for the false, but true just returns a result of
#N/A. I also tried typing =TODAY() in a cell off on the side and modifying
the formula to reference that cell, but the formula still returns #N/A for
the number of days passed if there was no order information.

I'm sure the adjustment is minor or there might even be an easier formula to
use. If someone could please help, I would appreciate it.

Thanks,
Christi

 

[Sean Timmons]

K2 doesn't technically have a value of "#N/A". So, instead, do..

=IF(iserror(K2),(NETWORKDAYS(E2,TODAY())-1),(NETWORKDAYS(E2,L2)-1))

 

[Peo Sjoblom]

Use

=IF(ISNA(K2),(NETWORKDAYS(E2,TODAY())-1),(NETWORKDAYS(E2,L2)-1))

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

If it has #N/A it might be better to use ISNA since another error type might
be of importance

--


Regards,


Peo Sjoblom

 

[Christi]

Thank you both for your assistance. Both suggestions worked.

Since I copied and pasted the values of my vlkp, and the value per se was
#n/a, why would the actual value still only be na in the formula? Just a
little curious.

 

[Sean Timmons]

Good point. Force of habit on my part...

If it has #N/A it might be better to use ISNA since another error type might
be of importance

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

Same here at times

--


Regards,


Peo Sjoblom

Good point. Force of habit on my part...

 

[Peo Sjoblom]

It's the same if you type false or true or paste them in a cell, they will
always be special
What I meant it doesn't matter if you paste or type the string, it won't be
regular text.
One thing that will show you that is the way it is aligned in the cell

TRUE, FALSE #N/A etc are center aligned whereas text is left aligned and
numbers are right aligned

Of course you need to have no alignment format in the cells in question plus
wide enough columns to see that

Only way to get a literal #N/A that can be compared to "#N/A"
is to precede it with an apostrophe or format the cell as text before it is
entered, if you type '#N/A
in A1 then this

=A1="#N/A"

should return TRUE



--


Regards,


Peo Sjoblom

 

[Christi]

I had never noticed that! I love learning new things about Excel, and today
you have enlightened me a little bit more. That tidbit will come in handy in
the future, I'm sure of it.

Thanks again for all your help