Dates and if statements

Good day!

I have had luck in the past with people helping me with my issue on this fourm so I am hoping someone here can help me with this I am sure obvious answer.

I am creating a database at work for expiry of various programs/courses. I have got that conditional formatting down but I am struggling to make D2 (if pers has course) change to yes, No, or expired depending on the dates. I have got it to say yes, and expired but the "No" part when no date is inserted in column E.

The following is the current IF function I am using in D2:

=IF(E4<=TODAY(),"Expired",IF(E4>=TODAY(),"Yes"))

I see the issue, but I just am not sure how to use multiple IF's, never works for me.

Any help would be greatly appriciated!

Hi Jade,

Am Tue, 19 Jun 2012 15:06:59 +0000 schrieb Jade Summers:

> I am creating a database at work for expiry of various programs/courses.
> I have got that conditional formatting down but I am struggling to make
> D2 (if pers has course) change to yes, No, or expired depending on the
> dates. I have got it to say yes, and expired but the "No" part when no
> date is inserted in column E.

try:
=IF(E4=0,"No",IF(E4<=TODAY(),"Expired","Yes"))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Originally Posted by Jade Summers

Good day!

I have had luck in the past with people helping me with my issue on this fourm so I am hoping someone here can help me with this I am sure obvious answer.

I am creating a database at work for expiry of various programs/courses. I have got that conditional formatting down but I am struggling to make D2 (if pers has course) change to yes, No, or expired depending on the dates. I have got it to say yes, and expired but the "No" part when no date is inserted in column E.

The following is the current IF function I am using in D2:

=IF(E4<=TODAY(),"Expired",IF(E4>=TODAY(),"Yes"))

I see the issue, but I just am not sure how to use multiple IF's, never works for me.

Any help would be greatly appriciated!

Hi,

How about:

=IF(E4="","No",IF(E4<=TODAY(),"Expired",IF(E4>TODA Y(),"Yes")))

One thing I will mention is in your original formula you should get rid of one of the equals. If you say <=today in one statement then >=today in the next, there is some overlap and today's date could theoretically appear in either statement. I've taken the last equals out in my suggested formula above as due to the order the second one would be ignored anyway.

Let me know how this works for you.

S.

Originally Posted by Spencer101

Hi,

How about:

=IF(E4="","No",IF(E4<=TODAY(),"Expired",IF(E4>TODA Y(),"Yes")))

One thing I will mention is in your original formula you should get rid of one of the equals. If you say <=today in one statement then >=today in the next, there is some overlap and today's date could theoretically appear in either statement. I've taken the last equals out in my suggested formula above as due to the order the second one would be ignored anyway.

Let me know how this works for you.

S.

It worked!

Thank you both so much for replying so quickly, and for the suggestions. I think my database is finally ready and my sanity is still intact, with your help of course