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?