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

 

[bobsan42]

multiple dates in a single cell do not count as dates - excel accepts this as a string.
A single cell can contain a single number, single date/time, text string or a formula.
if you need multiple dates you need to put each of them in a single cell.

P.S.: I apologize - i didn't see you try convert text to date. For an unknown reason the DATEVALUE returns #VALUE ! on my machine.

 

[bobsan42]

Your approach only allows you to check two dates, if you have three - you don't check the third one
And even for two you have to do some changes

[COLOR=#000000][FONT=Calibri]=OR( DATEVALUE(MID(A2,2,SEARCH(")",A2,2)-2)) < TODAY(), DATEVALUE(MID(A2,SEARCH("(",A2,2)+1,SEARCH(")",A2,[/FONT][/COLOR][COLOR=#ff0000][FONT=Calibri][B]13[/B][/FONT][/COLOR][COLOR=#000000][FONT=Calibri])[/FONT][/COLOR][COLOR=#ff0000][FONT=Calibri]-[/FONT]SEARCH("(",A3,2)-1[/COLOR][COLOR=#000000][FONT=Calibri]))< TODAY())[/FONT][/COLOR]

To be 100 % accurate the red 13 must actually be the length of the first date + 2 for the brackets + 1

I would go with several single date cells in Date format - much easier.

BTW, on my machine DATEVALUE reads the date in dd/mm/yyyy format rather than mm/dd/yyyy. Dates are best kept in date format IMHO.

 

[2phat4u]

Apologized for the confusion.
I need help on a multiple expiration date in a single cell. There are 2 dates in a single cell; example (4/30/2019)(6/30/2019). When one of the date in the single cell expired, it will turned red. Or, When one of the date in the single cell within 30 days, it will turned yellow. 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 1: cell A2 the dates are (4/30/2017)(10/31/2018) this one supposed to turn red

Expamle 2: Cell A2 the dates are (10/31/2019)(10/31/2019)(5/20/2019) this one supposed to turn yellow.


Thanks in advance

 

[bobsan42]

Did you look at my second post?

 

[2phat4u]

I did. I'm trying to understand what do I do for the A3 in that formula you provided SEARCH("(",A3,2). Do I change it to A2?

I did all of that and it didn't work. I added in cell A2 ((10/03/2019)(05/29/2019). It should havehighlighted yellow but didn’t.

Didn’t know what to do with the A3 in the SEARCH("(",A3,2)you provided.

 

[bobsan42]

sorry - my mistake - A3 has to be A2.
When I personally have to do a complicated formula I split it in simple pieces and first make them work 1 by 1. then I make them dependent on each other, then I combine them all.
Especially with Cond. format. formula - if you put the formula in a cell you will be able to see the errors and inspect the formula.

For this to work for me I had to change the dates from dd/mm/yyyy format to dd/mm/yyyy (otherwise I was getting VALUE error and CF was not working).
And again - this formula will only work for the first two dates - if you have more - they will be ignored.

I still suggest you use several cells for dates.

 

[2phat4u]

I dohave another question. Do you know how to write the conditional format if thecell is expired; example in cell A1 ISO 13485:2016 (04/05/2019). And if cell A1is within 30 days using the same content example ISO 13485:2016 (05/25/2019).

 

[bobsan42]

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[>

 

[2phat4u]

I want to say thank you for your help. I figured out the other half and it worked.
Example: if you have (05/25/2019)(12/03/2019) this will turn Yellow and (04/05/2019)(10/03/2019) this will turn Red.

1st select all cells and enter formula as follow.

Cells that contain
Yellow
less than
=NOW()+30




Cells that contain
Red
less than
=NOW()




Cells to format
clear
=ISBLANK(A1)=TRUE
Add a tick mark for Stop If True




Cells to format
Yellow
=OR( DATEVALUE(MID(A1,2,SEARCH(")",A1,2)-2)) > TODAY(), DATEVALUE(MID(A1,SEARCH("(",A1,2)+1,SEARCH(")",A1,2)-2))> TODAY() )






Cells to format
Red
=OR( DATEVALUE(MID(A1,2,SEARCH(")",A1,2)-2)) < TODAY(), DATEVALUE(MID(A1,SEARCH("(",A1,2)+1,SEARCH(")",A1,2)-2))< TODAY() )