Date formatting

[Turnipboy]

I have a cell which contains a date in the form dd.mm.yyyy e.g
27.01.2005, however it does seem to be formatted properly. I have a lot
of this data, how can I get excel to recognise it as a date and allow me
to alter the date format etc which I cannot do at the moment.

 

[Ron Rosenfeld]

I have a cell which contains a date in the form dd.mm.yyyy e.g
27.01.2005, however it does seem to be formatted properly. I have a lot
of this data, how can I get excel to recognise it as a date and allow me
to alter the date format etc which I cannot do at the moment.

Assuming you cannot format it now, it is probably text.

First backup your data.

If your data is in column A, in an adjacent column, enter the formula:

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

Copy/Drag down as far as needed.

Select this new column
Edit/Copy
Select A1
Edit/Paste Special Values


--ron

 

[Turnipboy]

Thanks, it did the trick.

 

[Ron Rosenfeld]

Thanks, it did the trick.

Glad to help.
--ron