SOLVED

# Converting numbers to excel time

Copper Contributor

# 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

2 Replies
best response confirmed by mathetes (Silver Contributor)
Solution

# Re: Converting numbers to excel time

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.

# Re: Converting numbers to excel time

Thank you very much Hans, this has worked.

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

# Re: Converting numbers to excel time

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.