Expired expiration date with text in the same cell

[2phat4u]

Hello,

I am looking for a formula that it would let me know when the expiration date going to be expired or has expired with the text in the same cell.
For example, Cell A1 contains "ISO 13485:2003 (exp. 2/2/2012)". How do I write the formula to let me know when the date has expired but the cell must include "ISO 13485:2003 (exp. 2/2/2012)"?

Is that even possible?



Help, please.

 

[oldbrewer]

My post was purely to test methodology eg are you stripping the correct date out

 

[2phat4u]

I’m not sure. Can you check this formula to see if there were errors? =if(today(=VALUE(IF(ISERROR((SEARCH("exp.",LEFT(A1,LEN(A1)-1))))," no",MID(LEFT(A1,LEN(A1)-1), SEARCH("exp.",A1)+5,99))))>0, "exp", "not exp").

My post was purely to test methodology eg are you stripping the correct date out

 

[oldbrewer]

(bbbb exp. 12/02/2012)		exp
		this formula puts corrrect date in C1
		=VALUE(LEFT(MID(A1,SEARCH("exp.",A1)+5,99),11))
		now I add the comparison with today
		=IF(VALUE(LEFT(MID(A1,SEARCH("exp.",A1)+5,99),11))<TODAY(),"exp","not exp")
		note today() you missed a bracket
		when I change it to 2012 it gives not expired
		so in the cond format it will be
		=VALUE(LEFT(MID(A1,SEARCH("exp.",A1)+5,99),11))>TODAY()
		this is definitely right because it returns false so cell will not color

<colgroup><col><col><col><col span="8"></colgroup><tbody>
</tbody>