Expired expiration date with text in the same cell

2

2phat4u

New Member
Joined
Aug 20, 2013
Messages
45
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.
 
Sort by date Sort by votes
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.


oldbrewer said:
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)
Click to expand...
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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?




oldbrewer said:
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
Click to expand...
 
Upvote 0
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
 
Upvote 0
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.



oldbrewer said:
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
Click to expand...
 
Upvote 0
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
 
Upvote 0
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.



oldbrewer said:
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
Click to expand...
 
Upvote 0
[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]
 
Upvote 0
[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]
oldbrewer said:
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
Click to expand...
 
Upvote 0
I don't want the date to be in a different cell. It must be in the same cell.


oldbrewer said:
[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]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Expiring Date formula?
Replies
5
Views
269
Alex Blakenburg
A
R
  • Question Question
Popup to Notify of Expiring/Expired Dates
2
Replies
11
Views
1K
Herakles
H
ExcelNewbie2020
  • Solved
Restructure list form from horizontal to vertical based on condition
Replies
2
Views
69
Fluff
Fluff
L
  • Question Question
Formula for upcoming expiation Dates
Replies
1
Views
529
kevin9999
K
Z
  • Question Question
Charting inventory by expiration date with projected levels based on sales
Replies
1
Views
1K
sijpie
sijpie

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top