Formatting dates after importing data

[Mick B]

Hope someone can help...

I have imported a large amount of data from a database application into a
new spreadsheet. There is a column that includes dates of birth in the format
,say, 28.07.1965 How can I format the cells in that column so that they read
in the format 28/7/1965. I have tried formatting the cells and trying
different variations but they dont seem to have any effect on the existing
data. I need to sort the data and the current formatting is not suitable.

 

[Gary''s Student]

With your data in a1, enter in B1:
=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) and sformat B1 to suit.

 

[Mike H]

Hi,

Select your column of dates then

data|Text to columns
Fixed width
Next
Next
Select Date DMY
Finish

Mike

 

[Mick B]

Great.

Thanks for your reply, what a time saver- cheers!

 

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>DMY

Format to Custom d/m/yyyy


Gord Dibben MS Excel MVP