Converting numbers to excel time

[Ainsley47]

Hello,

I'm wanting to know how to convert numbers with decimals into excel formatted time. I have thousands of cells so I can't change them all manually. 

For example, I have written the time in the cells as hours, minutes and seconds (eg. 8.36.10). I can format this cell individually but is there a more efficient way I can convert all the times I have written to excel time? I've tried some of the formulas online and they haven't worked for me.

Many thanks,

Ainsley

[#2 · Accepted solution]

@Ainsley47 

A number can have only one decimal separator, so 8.36.10 is not a number but a text value.

With such value in A2 and below, enter the following formula in B2:

=TIMEVALUE(SUBSTITUTE(A2,".",":"))

Format the cell with the formula as time, then fill down.

[#3]

@Hans Vogelaar 

Thank you very much Hans, this has worked.