Formula with 3 ifs

[julieabrell]

I am trying to figure out a formula that will leave a cell blank if another cell is anything other than an expired date, I have =IF(H4<(NOW()), "Expired",""), however for if H4 cell is blank the results are also expired.

[JohnTopley]

=IF(H4="","",IF(H4<TODAY(),"Expired","")

[FDibbins]

Hi, welcome to the forum

Another option...
=IF(OR(H4="",H4>=TODAY()),"","expired")

[jason.b75]

however for if H4 cell is blank the results are also expired.

Confused, are you saying that you want it to return "expired" if H4 is blank (formula blank) or that the formula does return "expired" when H4 is blank (empty cell, no formula) and you don't want it to?

It looks like John has assumed the first problem, while Ford has assumed the second, so hopefully one of them is right. If not, please try to be clearer with your explanation.

[julieabrell]

Thank you, this worked great!!

[julieabrell]

Thank you very much!!

[julieabrell]

Both of the above worked for what I needed, thank you.

[FDibbins]

It looks like John has assumed the first problem, while Ford has assumed the second, so hopefully one of them is right. If not, please try to be clearer with your explanation.

Actually, we are both doing teh same thing, just in a different way

[jason.b75]

I think I read John's formula incorrectly as =IF(H4="",IF(...

[sandy666]

I know it's solved...

my 2¢ without IF

=CHOOSE(OR(ISBLANK(H4),H4<TODAY())+1,"","Expired")

[FDibbins]

Sandy, that still leave "expired" is H4 is blank?

[sandy666]

Ford,
yes, with these two conditions: H4 = blank OR H4<TODAY()

OR(...) gives 0 or 1 and if 0 +1 = 1, or 1 +1 = 2, so with CHOOSE you have CHOOSE(1 or 2, "", "Expired")
Why +1 ? just because index cannot be 0

[jason.b75]

Ford,
yes, with these two conditions: H4 = blank OR H4<TODAY()

OR(...) gives 0 or 1 and if 0 +1 = 1, or 1 +1 = 2, so with CHOOSE you have CHOOSE(1 or 2, "", "Expired")
Why +1 ? just because index cannot be 0

But Ford was pointing out that your formula doesn't give the correct result, compare the results of your formula to the other suggestions when H4 is empty, or contains =""

my 2¢ without IF

How about this

=TEXT(N(H4)*(H4<TODAY()),"\expir\e\d;;;")

[FDibbins]

As Jason pointed out Sandy, your formula is not returning the correct result

[sandy666]

Right, I misunderstood first post.
for me conditions was: H4 must be clear or h4<today()
sorry about that

[FDibbins]

Nice use of CHOOSE though, great idea

[sandy666]

Thanks, but shame on me because I forgot about real "blank" and empty string "blank"

[jason.b75]

Thanks, but shame on me because I forgot about real "blank" and empty string "blank"

If you used LEN(H4)=0 or H4<>"" instead of ISBLANK(H4) then it would catch both.

[sandy666]

Jason, based on your idea with LEN()

=CHOOSE(AND(LEN($H4)<>0,$H4<TODAY())+1,"","Expired")


to be clear: expired only if date < today(), the rest is ""
or I misunderstood again?

===
update:

Please Login or Register  to view this content.

working with H4=

• date < today - (result: Expired)
• date > today - (result: "")
• real blank - (result: "")
• empty string ("") - (result: "")
• text (abc) - (result: "")
• generated error (#DIV/0, #N/A, etc.) - (result: "")
• negative numbers (-2) - (result: "")