Change in time where text is involved

J

Jill

I am working on a report where I am trying to show the
amount of time that passed between when we received a
completed contract and when the installlation occured. The
catch to this is I have three columns I am working with to
get this (1) Date the completed contract is received
(which is sometimes left blank) (2) Initial date set for
Install (which is sometimes left blank)and (3)a Possible
Revised Date if the Initial Date can not be meet (which is
often left blank...or filled with some text explaining "no
date" or "NA")
So what I am looking to do is say how much time passes
from the date the completed contract is received and the
greater of the two dates out of the Initial Install Date
and the Possible Revised Date.
The formula that I have been working on has covered all
the issues I have had with the eight different people
filling this thing out except the text people will put in
the "Possible Revised Date" column. When ever text falls
into the equation I get "#Value!" which I have been told
they want to avoid.
So finally getting to my point how do I pull this
information and have the equation show "NA" whenever there
is text in the Possible Revised Date column.

Currently my formula is:
=IF($H3<1,"NA",IF($K3<$M3,$M3-$H3,IF(($K3-$H3)
27000,"NA",IF($K3>$M3,$K3-$H3,IF($K3<1,"NA")))))

H= Date Completed Contract is Received
K= Initial Date for Install
M= Possible Revised Date
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top