I am trying to use a function to evaluate when a course is due. In column D
there may or may not be a date listed. I'm using this formula in to evaluate
if the course is due to be renewed.

=IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ")

The problem arises when D3 is blank. It is still evaluating the blank cell
as a date, 01/01/1907 and returning "due" because it is less than today.

If D3 is blank I need the formula to return an empty string, which I thought
the above did.

Try this

=IF(AND(D3<"",DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))< =TODAY()),"due"," ")

--
HTH

Bob Phillips

"Emily8" wrote in message
...
I am trying to use a function to evaluate when a course is due. In column
D
there may or may not be a date listed. I'm using this formula in to
evaluate
if the course is due to be renewed.

=IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ")

The problem arises when D3 is blank. It is still evaluating the blank
cell
as a date, 01/01/1907 and returning "due" because it is less than today.

If D3 is blank I need the formula to return an empty string, which I
thought
the above did.

On Tue, 11 Oct 2005 08:46:34 -0700, Emily8
wrote:

I am trying to use a function to evaluate when a course is due. In column D
there may or may not be a date listed. I'm using this formula in to evaluate
if the course is due to be renewed.

=IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY()," due"," ")

The problem arises when D3 is blank. It is still evaluating the blank cell
as a date, 01/01/1907 and returning "due" because it is less than today.

If D3 is blank I need the formula to return an empty string, which I thought
the above did.


You could do something like:

=IF(ISNUMBER(D3),IF(DATE(YEAR(D3)+3,MONTH(D3),DAY( D3))<=TODAY(),"due",""),"")

By the way, I would advise returning NULL strings rather than, as in your
formula, a <space character.


--ron

=if(d3="","",yourformulahere)

But any date 3 years in the future will never be <= today().

I'm not sure what that formula should be.

=if(d3="","",date(year(d3)+3,month(d3),day(d3)))
???


Emily8 wrote:

I am trying to use a function to evaluate when a course is due. In column D
there may or may not be a date listed. I'm using this formula in to evaluate
if the course is due to be renewed.

=IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ")

The problem arises when D3 is blank. It is still evaluating the blank cell
as a date, 01/01/1907 and returning "due" because it is less than today.

If D3 is blank I need the formula to return an empty string, which I thought
the above did.

--

Dave Peterson

Thanks Bob, Ron and Dave. It works perfectly with the AND, and I've altered
it to be a null string.

Dave, the admin types in the date the course was taken and then if it is
longer than 3 years ago, it comes up with "due". Not very elegant, but I
just needed to make the formula work! My users weren't very interested in
alternative methods to track this.

Thanks, All!

"Bob Phillips" wrote:

Try this

=IF(AND(D3<"",DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))< =TODAY()),"due"," ")

--
HTH

Bob Phillips

"Emily8" wrote in message
...
I am trying to use a function to evaluate when a course is due. In column
D
there may or may not be a date listed. I'm using this formula in to
evaluate
if the course is due to be renewed.

=IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ")

The problem arises when D3 is blank. It is still evaluating the blank
cell
as a date, 01/01/1907 and returning "due" because it is less than today.

If D3 is blank I need the formula to return an empty string, which I
thought
the above did.

Ooh. I see. I was (incorrectly) expecting the date in D3 to be more current.
That was a bad assumption!

Emily8 wrote:

Thanks Bob, Ron and Dave. It works perfectly with the AND, and I've altered
it to be a null string.

Dave, the admin types in the date the course was taken and then if it is
longer than 3 years ago, it comes up with "due". Not very elegant, but I
just needed to make the formula work! My users weren't very interested in
alternative methods to track this.

Thanks, All!

"Bob Phillips" wrote:

Try this

=IF(AND(D3<"",DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))< =TODAY()),"due"," ")

--
HTH

Bob Phillips

"Emily8" wrote in message
...
I am trying to use a function to evaluate when a course is due. In column
D
there may or may not be a date listed. I'm using this formula in to
evaluate
if the course is due to be renewed.

=IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ")

The problem arises when D3 is blank. It is still evaluating the blank
cell
as a date, 01/01/1907 and returning "due" because it is less than today.

If D3 is blank I need the formula to return an empty string, which I
thought
the above did.






--

Dave Peterson