Find oldest date in a cell containing multiple dates that aren't separated?

[Scuzzbopper]

I have a cell with a series of dates that are not separated like this:

05/04/201605/05/201605/05/201605/10/201605/12/201605/03/201605/12/201605/13/201605/16/201605/18/201605/19/201605/25/201605/25/201605/26/201605/27/2016

I would like to find the oldest date in the cell. I feel like I'm close with this formula:

=MIN(MID(A4,FIND("/",A4,1)-2,10))

But the formula is not complete as it just returns the first date in the cell. I'm not sure how to write the rest of the formula so it sort of "loops" through the cell and finds the oldest date.

I'm looking to accomplish this with a single formula because the report that this data is in is huge and cells with multiple dates like this are scattered throughout the report. An alternative would be a macro that I could use to highlight a cell hit the macro hotkey and have the contents of the cell change to only the oldest date in the cell but I'm a total noob to VBA and I have no idea where to start.

Similar to this exercise on Chandoo.

Any suggestions?

 

[NARAYANK991]

Hi ,

If your system date format is mm/dd/yyyy , then it is possible using a simple formula :

=MAX(IFERROR(0+(MID(G4,{1,11,21,31,41,51,61,71,81,91,101,111,121,131,141,151,161,171,181,191,201},10)),0))

This is an array formula , to be entered using CTRL SHIFT ENTER.

If your system date format is different , then VBA might be a better alternative.

Narayan

 

[bosco_yip]

Another option,

1] Data cell in A4.

2] Oldest date array formula (confirm pressing with SHIFT+CTRL+ENTER) :

=MIN(0+MID(A4,TRANSPOSE(ROW(INDIRECT("1:"&(LEN(A4)-LEN(SUBSTITUTE(A4,"/","")))/2)))*10-9,10))

3] Newest date array formula (confirm pressing with SHIFT+CTRL+ENTER) :

=MAX(0+MID(A4,TRANSPOSE(ROW(INDIRECT("1:"&(LEN(A4)-LEN(SUBSTITUTE(A4,"/","")))/2)))*10-9,10))

Regards
Bosco

 

[Scuzzbopper]

Another option,

1] Data cell in A4.

2] Oldest date array formula (confirm pressing with SHIFT+CTRL+ENTER) :

=MIN(0+MID(A4,TRANSPOSE(ROW(INDIRECT("1:"&(LEN(A4)-LEN(SUBSTITUTE(A4,"/","")))/2)))*10-9,10))

3] Newest date array formula (confirm pressing with SHIFT+CTRL+ENTER) :

=MAX(0+MID(A4,TRANSPOSE(ROW(INDIRECT("1:"&(LEN(A4)-LEN(SUBSTITUTE(A4,"/","")))/2)))*10-9,10))

Regards
Bosco

OMG this works perfect! I could kiss you!!!

Thank you!