Change the format of the date

[krishnanaik]

Hi!!

Please help

I have a list of approx 200 dates in the following format yyyymmdd (e.g 20060901). I would like to change this format to the following dd/mm/yyyy (e.g 01/09/2006).

What is the easiest way to do this?????

Many Thanks
Krishna

 

[Krishnakumar]

Hi,

=REPLACE(REPLACE(A1,5,0,"/"),8,0,"/")+0

Format the cell.

HTH

 

[erik.van.geit]

Hi,

in another column put
=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)
then paste the values

kind regards,
Erik

EDIT: to get the correct numberformat you might need to do a text-to-columns: in the last TAB select datataype "date"

 

[krishnanaik]

Cheers Guys. Much Appreciated. Have a fab day

 

[barry houdini]

EDIT: to get the correct numberformat you might need to do a text-to-columns.....

In fact you don't need a formula at all if you have a column of dates in that format.

You can convert in situ

Select Column
Data > Text to Columns
At step 3 choose date option with format YMD

edit: although if you did want to use a formula.......

=TEXT(A1,"0000-00-00")+0

format as date