If statement containing today formula

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

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

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

"Christi" wrote:

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

Use

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

--


Regards,


Peo Sjoblom

"Christi" wrote in message
...
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

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

--


Regards,


Peo Sjoblom

"Sean Timmons" wrote in message
...
K2 doesn't technically have a value of "#N/A". So, instead, do..

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

"Christi" wrote:

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

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.

"Peo Sjoblom" wrote:

Use

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

--


Regards,


Peo Sjoblom

"Christi" wrote in message
...
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

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

"Peo Sjoblom" wrote:

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

--


Regards,


Peo Sjoblom

"Sean Timmons" wrote in message
...
K2 doesn't technically have a value of "#N/A". So, instead, do..

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

"Christi" wrote:

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

Same here at times

--


Regards,


Peo Sjoblom

"Sean Timmons" wrote in message
...
Good point. Force of habit on my part...

"Peo Sjoblom" wrote:

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

--


Regards,


Peo Sjoblom

"Sean Timmons" wrote in message
...
K2 doesn't technically have a value of "#N/A". So, instead, do..

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

"Christi" wrote:

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

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

"Peo Sjoblom" wrote:

Use

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

--


Regards,


Peo Sjoblom

"Christi" wrote in message
...
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

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

"Peo Sjoblom" wrote:

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

"Peo Sjoblom" wrote:

Use

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

--


Regards,


Peo Sjoblom

"Christi" wrote in message
...
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