Format race times with hundredths of a second from 1.25.67 to 1:25.67

[MichelleLink]

Hello, is there a way to format a race time with hundreds of a second from 1.25.67 to 1:25.67 automatically? Basically change that first decimal to a colon so it reads mm:ss.00
I have thousands to do and I am trying to calculate split times. Thanks!

 

[FormR]

Hi, you could use a formula in an adjacent cell to convert that value into a proper Excel time.

For example:

=0+SUBSTITUTE(A1,".",":",1)

You could then custom format the cell that contains the formula as [mm]:ss.000 and perform your calculations upon it.

 

[mohadin]

Hi
Option 2

Sub testr()
a = Range(Cells(2, 1), Cells(12, 1).End(xlDown))
For i = 1 To UBound(a)
a(i, 1) = Split(a(i, 1), ".")
a(i, 1) = a(i, 1)(0) & ":" & a(i, 1)(1) & "." & a(i, 1)(2)
Next
With Cells(2, 2).Resize(UBound(a))
.Value = a
.NumberFormat = "mm:ss.000"
End With
End Sub

 

[MichelleLink]

Hi, you could use a formula in an adjacent cell to convert that value into a proper Excel time.

For example:

=0+SUBSTITUTE(A1,".",":",1)

You could then custom format the cell that contains the formula as [mm]:ss.000 and perform your calculations upon it.

Thank you SO much, that worked great!