Excel - Format of dates doesn't change 1

[Livia8]

Hi there,

I've a spreadsheet containing dates and times as numbers (i.e. 17:30 appears to be 0.729166666666667). If I look at the format of the cells it's text, but even if I change it to date (or time, depending on the cells), the actual format won't change unless I double click on each cell and then press enter. Now, I have 8 columns with over 6,500 rows, so this is not a realistic option. I've also tried to copy and paste special, which also doesn't work. I've tried to copy the columns to word and then over to excel again as text, and then to change the format back to date/ time: this DOES work but it takes over 30 minutes for the system to finish the process (also not a realistic option).
Any suggestions?
Thanks.

Thanks.

 

[cheerio]

If A1 is such a cell putting the formula =A1*1 in an adjacent column should turn the text into a number.

You should then be able to paste that column back over column A as values.

 

[Livia8]

That's perfect, thanks!