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.

 

[2phat4u]

Yes, each time the date expired, I have to change it in the same cell. Which is why it is still red when i changed the year. So, what you're saying by changing the year on that date it will still be red.

let us go back to basics - say cell A1 can only have 3 values, 1,2 or 3
first cond format formulaa will be =A1=1 and set to red
put 1 in A1 and A1 goes red
now without YET adding another formula, make A1=2
A1 loses its red color, it cannot be red because it does not equal 1

did you change the cell in my example to ISO 13485:2003 (exp. 2/2/2020)

 

[oldbrewer]

it only goes red if the expiry date has passed - so check if you have entered the formula correctly - try out in a new dpreadsheet when A1 =1 it goes red any other value no color will be applied

 

[2phat4u]

Guess that formula is too complicated. Let make this simple as possible. Cell A1 contains ISO 13485:2003 (2/2/2012). This time is w/o the exp. Can you show me the formula where it will turn red when the expiration date expired?


If possible, can you show me the formula for the same cell when it is within 30 days before expiring?

it only goes red if the expiry date has passed - so check if you have entered the formula correctly - try out in a new dpreadsheet when A1 =1 it goes red any other value no color will be applied

 

[oldbrewer]

no because it might be 02/02/2012 or 02/02/12

if you can format all dates as dd/mm/yyyy then it is much simpler

 

[2phat4u]

I tried pluggin in the formula for A1. It turns red for the year 2012.

Under Cond. Formatting, I used "Use a formula to determine which cells to format" in Rule Type. That is where I plugged in your formula.

Here is what I have for A1 cell ISO 13485:2003 (exp. 02/02/2012 just like you had. When I changed just the year to 2022, it's still red. The red was supposed to go away when the year is changed.


I don't know what I'm doing wrong. Please help.

no because it might be 02/02/2012 or 02/02/12

if you can format all dates as dd/mm/yyyy then it is much simpler

 

[oldbrewer]

you have a newer version of excel than me. if it went red the formula "worked". I have no idea why the red did not disappear when you changed it to 2022.

Are you able to influence how the data comes into you ? Copy the date as is with 2022 and paste it here and I will try to work with it

 

[2phat4u]

I am using a 2010 version of excel. The data is simple I just type ISO 13485:2003 (exp. 02/02/2022) into A1 cell.

you have a newer version of excel than me. if it went red the formula "worked". I have no idea why the red did not disappear when you changed it to 2022.

Are you able to influence how the data comes into you ? Copy the date as is with 2022 and paste it here and I will try to work with it

 

[oldbrewer]

[TABLE="width: 754"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]ISO 13485:2003 (exp. 02/02/2012)[/TD]
[TD][/TD]
[TD]02/02/2012)[/TD]
[TD="align: right"]10[/TD]
[TD]02/02/2012[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=MID(A1,SEARCH("exp.",A1)+5,99)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=LEN(C1)-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=LEFT(C1,D1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]do this with your set up and change to 2022 - does the date in cell E1 change correctly ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[2phat4u]

[TABLE="width: 1079"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 9"]Went back and reread what you wrote. You wrote my reply showed you how to pull the date out
so you need =if(today() - put formula to strip out date here - >0, "exp", "not exp").


So, I substitute the formula stripping out the date into =if(today() - put formula to strip out date here - >0, "exp", "not exp").


Which the final formula will be =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"). Can you check it?



[/TD]
[/TR]
</tbody>[/TABLE]

you have a newer version of excel than me. if it went red the formula "worked". I have no idea why the red did not disappear when you changed it to 2022.

Are you able to influence how the data comes into you ? Copy the date as is with 2022 and paste it here and I will try to work with it

 

[2phat4u]

I don't want the date to be in a different cell. It must be in the same cell.

[TABLE="width: 754"]
<tbody>[TR]
[TD]ISO 13485:2003 (exp. 02/02/2012)[/TD]
[TD][/TD]
[TD]02/02/2012)[/TD]
[TD="align: right"]10[/TD]
[TD]02/02/2012[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=MID(A1,SEARCH("exp.",A1)+5,99)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=LEN(C1)-1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=LEFT(C1,D1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]do this with your set up and change to 2022 - does the date in cell E1 change correctly ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]