I have several dates in a cell. Each date is separated by a comma...what formula should I use to get in return the earlierst date in the cell? For example, in A1 i have 01JUN12, 15JUN12 and 19JUN12...I would like as return 01JUN12 since that is the earliest date in the cell.

Let's clarify something first. You said:

Each date is separated by a commaThen you said:

in A1 i have 01JUN12, 15JUN12 and 19JUN12Those dates are not separated by a comma. Do you mean that in A1 you have this?

01JUN12, 15JUN12, 19JUN12Assuming that is correct, my next question is:

Will the first "date" in that string always be the earliest?

Finally, I should point out that Excel is not going to recognize those strings as Dates. Excel will see them as Text Strings. The only way to have Excel recognize each individual text string as a Date is to separate them into individual cells and then format them as Dates. The Text-To-Column feature will allow you to split the full string at each comma and place each string into a separate cell.

Once Excel can recognize each string as a date, the MIN or SMALL function should be able to determine the "earliest date".

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Here is a very long, very ugly formula that should get you what you want.

It will work only with three dates in a cell,

each date must 7 characters long and

be separated by a comma and a space

so your, data should look like:01JUN12, 15JUN12, 19JUN12

With your data in cell A1 enter the formula in cell B1, don't forget to format B1

as a Date Cell:=IF(AND(DATEVALUE(LEFT($A$1,7))<DATEVALUE(MID($A$1,9,8)),DATEVALUE(LEFT($A$1,7))<DATEVALUE(RIGHT($A$1,7))),DATEVALUE(LEFT($A$1,7)),IF(AND(DATEVALUE(MID($A$1,9,8))<DATEVALUE(LEFT($A$1,7)),DATEVALUE(MID($A$1,9,8))<DATEVALUE(RIGHT($A$1,7))),DATEVALUE(MID($A$1,9,8)),IF(AND(DATEVALUE(RIGHT($A$1,7))<DATEVALUE(LEFT($A$1,7)),DATEVALUE(RIGHT($A$1,7))<DATEVALUE(MID($A$1,9,8))),DATEVALUE(RIGHT($A$1,7)),"x")))

Copy/paste from here so you don't have to do it by hand..

I would also advise taking DerbyDad's advice and separate out the dates into

their own cells. It will be sooooo much easier to work with them.MIKE

Ask Your Question

Weekly Poll