rationalizing date format in column

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
 
N

Niek Otten

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Today's Date 1
date format 6
Find a date in list less or equal than target date 4
Dates converted into text 5
Date format. 1
Finding Date in an overseas format 20
Date formatting 1
Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format date? 4

Top