rationalizing date format in column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Back
Top