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?

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?

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?

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" 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?

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

"Joll" wrote in message
...
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" 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?