cell format - not automatically taking effect

[Joll]

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?

 

[David Biddulph]

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.

 

[DILipandey]

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.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Joll]

David

Thank you for the swift response.

I tried the test and the cells were not text (i.e. the test result was
"false"). Then I worked a bit harder on it and discovered that some were and
some weren't (no idea why). So, your advice was good and the problem is now
solved - many thanks!

J

 

[David Biddulph]

Glad to hear it. Thanks for letting us know that it's sorted.