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

 

[Peter_SSs]

With the single date in A1 and the multiple dates in B1, try this in C1. If you could have more than 10 dates in B1, increase the red 10's accordingly.

=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))<<1),1)

 

[Aaronsmity]

With the single date in A1 and the multiple dates in B1, try this in C1. If you could have more than 10 dates in B1, increase the red 10's accordingly.

=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))<<1),1)

Thank you for your time, this throws an error for me putting the dates and formulas in the cells you mentioned. I am trying to wrap my head around why the entire A column is being used in the index function. Ideally this would be a formula I could drag down in column C to evaluate each row independently based on data in column A and B for that row. Thanks for your help on this.

 

[Rick Rothstein]

I would guess this is not the most efficient formula, but this array-entered** formula seems to work. Note that it provides for a comma-delimited text string of dates that are no longer than 300 character. If the cells in Column B could contain longer text strings, the increase all of the 300s to a number larger than the maximum number of characters you would expect in those cells.

=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))))


**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Peter_SSs]

this throws an error for me putting the dates and formulas in the cells you mentioned.

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

I am trying to wrap my head around why the entire A column is being used in the index function.

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.

Ideally this would be a formula I could drag down in column C to evaluate each row independently based on data in column A and B for that row.

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

Book1
	A	B	C
1	1/10/2017	1/9/2017,12/10/2017,20/9/2017,8/10/2017	20/09/2017
2	5-Sep-16	12-Oct-2017, 12-Oct-2016, 12-Oct-2015,15/12/17	12/10/2015
3	25/01/2017	24/01/2017,25/1/17	24/01/2017
Get Date

Cell Formulas
Range	Formula
C1	=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))),1)
C2	=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))),1)
C3	=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))),1)

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

 

[Rick Rothstein]

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

You are right, of course, the formula I posted did not display correctly (it was posted correctly, but did not display correctly). At first, when I read you comment, I figured I had been tripped up by the less than symbol (and this forum's tendency to see that as the start of an HTML tag), but your full version of what I posted did nothing special around the less than sign, but it displayed correctly for you... how did you get it to do that? I don't see that you did anything special at all, yet yours posted correctly. Why?

 

[Peter_SSs]

.. I figured I had been tripped up by the less than symbol ..

You had.

.. but your full version of what I posted did nothing special around the less than sign, but it displayed correctly for you... how did you get it to do that?

After pasting the formula in my reply but before submitting, I replace the < with <
Note that the problem only occurs if the < sign is followed immediately by a letter. So the following are all okay as is A1<1, A1<$B1, A1<"Z"
Another option if you don't want HTML elsewhere in your post, is in your reply to 'Go Advanced' where there is an option to turn off HTML for that particular post, which is what I have done in this post so that my < did not resolve to a < sign.

Edit: Also note that when you quoted my post, the problem re-occurred. It didn't delete the whole of the rest of the formula because I had broken the formula into 2 lines so you just lost the rest of the line that the < was on.

 

[Rick Rothstein]

Another option if you don't want HTML elsewhere in your post, is in your reply to 'Go Advanced' where there is an option to turn off HTML for that particular post, which is what I have done in this post so that my < did not resolve to a < sign.

How long has that option been there in "Go Advanced"? Is it new with the forum update? I don't recall seeing it before or, if I had, it did not dawn on me exactly what it meant.

 

[Peter_SSs]

How long has that option been there in "Go Advanced"?

Forever as far as I can remember. Certainly long before the recent update.

BTW, did you see my edit of my previous post?

 

[Rick Rothstein]

Forever as far as I can remember. Certainly long before the recent update.

Well, oblivious me never noticed it then.

BTW, did you see my edit of my previous post?

Yes, I saw it. Interesting... when I look at your post, I see the less than symbol, not the &lt. Perhaps it was converted to a less than sign before you evoked the No HTML option and is it the later that "froze" it in place when I look at your message? Then, when I quoted your message, because I did not evoke the No HTML option I guess, it converted the less than symbol to the start of a tag in my reply.