Select max date in comma separated cell only if it is less than a date in another cell

[Aaronsmity]

In one column I have a single date and in another column I have multiple dates separated by a comma. I am trying to figure out a formula that will return the max date in the second column only if it is before the date in the first. So the answer to the date I would like it to find in the below example would be 9/20/2017. The number of date varies in the second column and does not go in any kind of logical order. Thanks for any help on this!

10/1/2017 ----- 9/1/2017,10/12/2017,9/20/2017,8/10/2017

 

[Aaronsmity]

What error, and what are the values in A1 and B1 that gave you the error?


I used that structure to avoid using a volatile function like INDIRECT as Rick has used in his suggestion. It is just that volatile functions can slow your sheet considerably if you have a lot of them in the sheet. Essentially, that index structure, and Rick's ROW(INDIRECT( structure are used to break the column B string into individual dates to be able to compare them to the column A date.


You can. Here is mine copied down a few rows. Note that my dates are in d/m/y format.

	A	B	C
1/9/2017,12/10/2017,20/9/2017,8/10/2017
12-Oct-2017, 12-Oct-2016, 12-Oct-2015,15/12/17
24/01/2017,25/1/17

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1/10/2017[/TD]

[TD="align: right"]20/09/2017[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5-Sep-16[/TD]

[TD="align: right"]12/10/2015[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]25/01/2017[/TD]

[TD="align: right"]24/01/2017[/TD]

</tbody>

Get Date​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=AGGREGATE(14,6,DATEVALUE(MID(SUBSTITUTE(B1,",",REPT(" ",100)),100*ROW(INDEX(A:A,1):INDEX(A:A,10))-99,100))/(DATEVALUE(MID(SUBSTITUTE(B1,",",REPT(" ",100)),100*ROW(INDEX(A:A,1):INDEX(A:A,10))-99,100))<A1),1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=AGGREGATE(14,6,DATEVALUE(MID(SUBSTITUTE(B2,",",REPT(" ",100)),100*ROW(INDEX(A:A,1):INDEX(A:A,10))-99,100))/(DATEVALUE(MID(SUBSTITUTE(B2,",",REPT(" ",100)),100*ROW(INDEX(A:A,1):INDEX(A:A,10))-99,100))<A2),1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=AGGREGATE(14,6,DATEVALUE(MID(SUBSTITUTE(B3,",",REPT(" ",100)),100*ROW(INDEX(A:A,1):INDEX(A:A,10))-99,100))/(DATEVALUE(MID(SUBSTITUTE(B3,",",REPT(" ",100)),100*ROW(INDEX(A:A,1):INDEX(A:A,10))-99,100))<A3),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



BTW, the formula Rick meant to post was
=MAX(IF(0+TRIM(MID(SUBSTITUTE(","&B1,",",REPT(" ",300)),ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))*300,300))<A1,0+TRIM(MID(SUBSTITUTE(
","&B1,",",REPT(" ",300)),ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1))*300,300))))

Thanks for all your help, your aggregate with index function worked like a charm!

 

[Peter_SSs]

Thanks for all your help, your aggregate with index function worked like a charm!

Good news. Sounds like you must have resolved whatever the problem was that you referred to in post #3 .