Solved Multiple dates in a cell

September 30, 2013 at 11:04:13
Specs: Windows 7
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.

See More: Multiple dates in a cell

Report •

September 30, 2013 at 13:14:34
Let's clarify something first.

You said:

Each date is separated by a comma

Then you said:

in A1 i have 01JUN12, 15JUN12 and 19JUN12

Those dates are not separated by a comma. Do you mean that in A1 you have this?

01JUN12, 15JUN12, 19JUN12

Assuming 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.

Report •

September 30, 2013 at 13:41:03
✔ Best Answer
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:


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.


Report •

Related Solutions

Ask Question