rationalizing date format in column
 

I have an excel spreadsheet with a date column with the cells in the column
formatted as Date "dd/mm/yy". However, some of the cells contain the date in
other forms such as: dd.mm.yy or ddth month yyyy (the column format doesn't
seem to have had any effect on these). Is the way to resolve this problem to
write a macro to convert the unconverted and if so, what would the code be?

Many thanks

Pete

rationalizing date format in column
 

Hi Pete,

These "dates" are text for Excel.
If the format is consistent, you can derive a date from it with a formula
like

=DATE(RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

Adjust the formula for the other format or let the formula sort the format
out itself.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel



"Italian Pete" wrote in message
...
I have an excel spreadsheet with a date column with the cells in the column
formatted as Date "dd/mm/yy". However, some of the cells contain the date
in
other forms such as: dd.mm.yy or ddth month yyyy (the column format
doesn't
seem to have had any effect on these). Is the way to resolve this problem
to
write a macro to convert the unconverted and if so, what would the code
be?

Many thanks

Pete