Multiple expiration date in a single cell

[2phat4u]

Hello,
I need help on a multiple expiration date in a single cell.I have these conditional formatting formula in place but for some reason it isn’tworking when there a multiple date in a single cell.
Red
Rule Type
Use a formula to determine which cells to format
=OR( DATEVALUE(MID(A2,2,SEARCH(")",A2,2)-2)) < TODAY(), DATEVALUE(MID(A2,SEARCH("(",A2,2)+1,SEARCH(")",A2,2)-2))< TODAY() )
Clear
Rule Type
Use a formula to determine which cells to format
=ISBLANK(A2)=TRUE
add a tick mark for Stop If True

Red
Rule Type
Format only cells that contain
Cell Value >>>> Less than >>>> =NOW()

Yellow
Rule Type
Format only cells that contain
Cell Value >>>> Less than >>>> =NOW()+30



Example: cellA2 the dates are (4/30/2017)(10/31/2018) this one supposed to turn red

CellA3 the dates are (10/31/2019)(10/31/2019)(5/20/2019) this one supposed to turn yellow.


Thanks in advance

 

[2phat4u]

Yes, this is the one I'm looking for. That formula only works with a single data in the cell with just one color...example in A1 cell ISO 13485:2016 (05/05/2019), it will be yellow. it can turn Red if I change the color to red and make the date expired.

Are there anyway to make it turn red with a separate formula?

Also, if it not to much to ask. Are there any formula to make A1 cell turns Red (ISO 13485:2016 (04/25/2019))(ISO 13485:2016 (06/25/2019))?

is this what you mean?

=TODAY()+30>DATEVALUE(MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1))

<today()+30[ code]<today()+30[="" code]<="" html=""></today()+30[>

 

[bobsan42]

depending on your Excel version you can add at least 3 separate conditional formats - red, yellow, cyan, black, whatever
if you remove +30 from the formula above it will highlight cell with date in the past (ALL dates before today).

However - two or more dates with a lot of brackets will lead to quite a long CF formula.
Depending on the amount of data you have in your file this may degrade performance.

And frankly - too complicated CF formulas are hard to keep in order. I would extract all dates to separate cells and then base CF on these cells.

 

[2phat4u]

Cell A1 enter as follow:
Material (08/01/2019)
Material (04/01/2019)
Material (07/01/2019)

When I put the formula =TODAY()>DATEVALUE(MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)) it doesn't turn red. However, when i changed the date from 08/01/2019 to 05/01/2019, it turned red. Please help.

 

[bobsan42]

it seems to be reading the dates in format mm/dd/yyyy, meaning 08/01/2019 is the first of august and 05/01/2019 is the first of may - so the coloring is consistent in this case since today is the 3rd of May.

 

[2phat4u]

yes, that is correct. the format is in mm/dd/yyyy.

How come it didn't turn red?
Cell A1 enter as follow:
Material (08/01/2019)
Material (04/01/2019)
Material (07/01/2019)

what did i do wrong?

 

[bobsan42]

ok, I will try to explain again:
I assume that you want to color the cell red if the date in the brackets is passed
08/01/2019 is actually 1st of august so it is not in the past: today()> 08/01/2019 evaluates to FALSE so the cell will not turn red
The formula above(=DATEVALUE(MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1))), as I believe I explained in one of my previous posts, will only return the the value of the first date in brackets.
So if you expect that based on the second date (04/01/2019) the cell will turn red you are wrong.
I also tried to explain before that extracting more than one date with a formula from the text string will be a tedious task and will end up with a very long formula. Which is not good in terms of performance and maintenance.

 

[2phat4u]

Ok, thank you again.

ok, I will try to explain again:
I assume that you want to color the cell red if the date in the brackets is passed
08/01/2019 is actually 1st of august so it is not in the past: today()> 08/01/2019 evaluates to FALSE so the cell will not turn red
The formula above(=DATEVALUE(MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1))), as I believe I explained in one of my previous posts, will only return the the value of the first date in brackets.
So if you expect that based on the second date (04/01/2019) the cell will turn red you are wrong.
I also tried to explain before that extracting more than one date with a formula from the text string will be a tedious task and will end up with a very long formula. Which is not good in terms of performance and maintenance.