change date format

[Guest]

I have a column of dates in an excel spreadsheet in the format 05-04-06. How
do I the change dates to 05/04/06.

I have tried formatting the cells but this does not work. Does anyone have a
formula that can be used please?

 

[Otto Moehrbach]

Goldie
Ordinarily Excel would recognize that format as a date and change it the
way you want automatically. I don't know the source of these dates but
Excel apparently thinks of them as text. Try this with one such cell and
see what you get. Say the cell is A1. In some out-of-the-way cell, type
=A1*1. This should produce a 5-digit number. That is a date. Format that
cell as you like and you should get what you want. There are other ways of
doing this so if this doesn't work for you, post back.
If it works, now you want to do the same to all such cells. Here's a
quick way. In some out-of-the-way cell type "1" without the quotes. Now
with that cell selected, click on Edit - Copy. Now select all the cells
that you want changed. Click on Edit - PasteSpecial and select Multiply
then OK. Then format all the cells like you want. HTH Otto

 

[Gord Dibben]

Goldie

Alternate method..........

Select column of dates.

Data>Text to columns>Next>Next>Data Format Type>Date. Pick DMY or MDY and
Finish.


Gord Dibben MS Excel MVP