Forum Discussion
Converting numbers to excel time
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
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.
2 Replies
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.
Thank you very much Hans, this has worked.
