Cell format will not update

[Joll]

Hi

I keep running across the same problem with a large project and it's driving
me crazy.

I have a worksheet with data already provided in this format:

dd/mm/yyyy hh:mm:ss

To be worked with, it needs to be transferred into this format:

yyyy/mm/dd hh:mm:ss

I set up a custom format to perform the required change, the trouble is that
even though the selected list of data has had the cell format applied using
format painter, it does not automatically update the data. Saving, closing
and reopening does not perform the format change either, nor does ctrl F9.
The only thing that works is to click into each cell and hit enter. Hey
presto, it updates.

Is there any other way of forcing the format change? I have some extremely
large chunks of data to work through.

Thanks!

Joll

 

[HARSHAWARDHAN. S .SHASTRI]

Hi jOLL,

If data is already avl then you can use following formula

=TEXT(A1,"yyyy/mm/dd hh:mm")

H S Shastri