just an additon to what David has answered:-
Type one (1) somewhere and copy it. Now select the date range and paste -
special values : multiply.
It will also do the needful.

Thanks.
--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)


New Delhi, India


"David Biddulph" wrote:

It should change as soon as you apply the new format. I wonder whether your
cells may actually be text, rather than real Excel dates and times. Try
=ISTEXT(cellref) and =ISNUMBER(cellref). If ISTEXT is true, you'll need to
convert from text to number. Techniques which may work include:
Data/Text to columns, or
Copy a blank cell, then select your text dates, and Edit/ Paste Special/
Add.
--
David Biddulph

"Joll" wrote in message
...
I am trying to process a large amount of data that has the format
dd/mm/yyyy
hh:mm:ss and needs to be converted into yyyy/mm/dd hh:mm:ss. I have
successfully created a custom cell format for the date, but the cells will
only change when I double click on them, which is not practicable for the
amount of data I have. Format painting does not automatically change the
cells. Paste special does not work and I have also tried saving & closing
the worksheet, to no avail. Anyone got any clues?