IF Formula Question

[yooheest]

Hi, Ive been for this report that i cant figure how to work.


Can someone help me with a formula that will display expired remarks.
For example i have
=IF(k5>NOW,k5<<now,if(m5>NOW,IF(m5><now,if(o5>NOW,m5<NOW,IF(o5><now,if(s5>NOW,o5<NOW,IF(s5>NOW,s5<NOW))<now))

k5, m5, o5, s5 are the columns that contains date of expiration, i want the expiration dates computed and displayed on another column if they are expired or not with remarks as Expired certificate. Is this possible??

Looking forward for any assistance, thank you..</now))
</now,if(s5></now,if(o5></now,if(m5>

 

[Scott T]

The forum did not post your whole formula put spaces around any < so the forum does not think it is part of an HTML tag. The now function needs to have () like
only your formula would not have the spaces

=IF(k5>NOW(),[COLOR=#ff0000]k5 < NOW()[/COLOR],..

The part I read is a logical test but it is in the TRUE augment of the IF statement is that what you really want?

 

[SyedUsman]

Try This

=IF(NOW()>K5,"Expired Certificate","Valid Certificate")

 

[yooheest]

Try This

=IF(NOW()>K5,"Expired Certificate","Valid Certificate")

this might work, but how about the other cells??
i have to input at least 5 cells on the argument and validate their expiration dates and post it on the last column if its expired or not.

im having a hard time figuring this out.

thanks for assistance guys.

 

[Scott T]

If you want to show expired in a cell if any of the dates are expired then this should work

=IF(OR(NOW()>K5,NOW()>M5,NOW()>O5,NOW()>S5),"Expired Certificate","Valid certificate")

 

[SyedUsman]

this might work, but how about the other cells??
i have to input at least 5 cells on the argument and validate their expiration dates and post it on the last column if its expired or not.

im having a hard time figuring this out.

thanks for assistance guys.

Just reference those cell in another column & copy the formula.

If you want to know if any one of the certificate has expired then try Scott T's Formula but you then wouldn't know which certificate expired.

 

[yooheest]

How about if i want to display 2 or more expired cell in the last column? for example: cell K5, M5 and O5 dates are expired these cells have different remarks in it and i want to display their value on the last part of my column. Could this work??


=IF(OR(NOW()>K5,NOW()>M5,NOW()>O5,NOW()>S5),"Expired Certificate","Expired certificate 1 certificate",Expired Certificate 2)

 

[Scott T]

The if statement only has one TRUE and one FALSE augment. You would need multiple if statements something like.

=IF(NOW()>K5,"Expired Certificate","")&" "&IF(NOW()>M5,"Expired Certificate 2","")&" "&IF(NOW()>O5,"Expired Certificate 3","")&" "&IF(NOW()>S5,"Expired Certificate 4","")

If all were expired would return
Expired Certificate Expired Certificate 2 Expired Certificate 3 Expired Certificate 4

 

[yooheest]

Got it!

Thank you so much guys! much appreciated, more power to this forum.

 

[Scott T]

If you want commas to separate the results try

=IF(NOW()>K5,"Expired Certificate","")&IF(AND(NOW()>K5,OR(NOW()>M5,NOW()>O5,NOW()>S5)),", ","")&IF(NOW()>M5,"Expired Certificate 2","")&IF(AND(NOW()>M5,OR(NOW()>O5,NOW()>S5)),", ","")&IF(NOW()>O5,"Expired Certificate 3","")&IF(AND(NOW()>O5,NOW()>S5),", ","")&IF(NOW()>S5,"Expired Certificate 4","")