One Cell - Multiple Dates - Formatting

S

Stacy

I am not sure if this can be done.
I am working on Excel 2002.

I will try to make it short and still be understandable......
Here goes:

I am receiving a file from someone that has a field for "Date of
Birth". In some cases I have people using multiple dates of birth, so
in one cell I have 2 (or more) dates of birth.

My issue is the original format of the dates are in dd/mm/yyyy format
(this can not be changed, this file is coming from the UK)

If there is one date of birth, I can just change the format to
mm/dd/yyyy and everything looks fine, however, if I have multiple
dates, I can't just change the format.

Is there a way to automatically change multiple dates within the same
cell without having to manually change every one individually?

Thanks in advance for any suggestions.
 
O

Otto Moehrbach

Stacy
One way is to use VBA.
Another way involves a bit of work for you. First, use Data - Text To
Columns to separate all the dates into separate columns (make sure the other
columns are blank first). Then you have each date in a separate column.
But the dates are 5-digit numbers and the cells are formatted as text.
Format those cells as Date. Then use a formula like:
=TEXT(C1,"mm/dd/yy;@") & " " & =TEXT(D1,"mm/dd/yy;@")
to bring the dates back into one cell.
A bit tedious I would say.
I would choose the VBA route myself. Once you have the VBA procedure you
could simply select the cell, hit a specific key combination (or button if
you wish) and the change would be made instantly
If you want to go with VBA, send me direct via email a small file with some
samples of entries you have with multiple dates. Make sure you copy the
values directly from the actual cells. The layout of the dates within these
cells is what I need. My email address is [email protected]. Remove
the "nop" from this address. HTH Otto
 
Top