Changing multiple cell data formats

[mj-mik]

Dear,

I have a sheet with data as general format and i want to change it to time format. I can do that with going to formats but after i apply it does not change unless i double click on it, I want a way to change it without double clicking on it and then copy that format to all without the need to double click on any.

Please help.

[#2 · Accepted solution]

@mj-mik 

That suggests that the values are seen as text.

Try the following:

• Select the data in a single column.
• On the Data tab of the ribbon, click Text to Columns.
• Without changing any settings, click Finish.
• Format the selection as time, if necessary.

Alternatively (in particular if you have multiple columns with such values):

• Enter 0 in an empty cell.
• Select that cell and copy it (Ctrl+C).
• Select the data.
• On the Home tab of the ribbon, click the lower half of the Paste button.
• Select Paste Special...
• Select Add.
• Click OK.
• Format the selection as time, if necessary
• Clear the cell in which you entered 0.

[#3]

Dear Hans,

The first option worked perfectly.

Thank you.