Time milliseconds format

[Kaykayme]

I have a spreadsheet with data gathered from an HTML file. The time field
shows the usage in elasped time. Some of the data has the standard elapsed
time format "13:36:17" and some show the elapsed time with milliseconds
"37:10:46:54". I have tried changing the number format to the standard
elapsed time format with a decimal to show milliseconds but not all converted
because the field uses AM and PM at the end. I tried copying the text of
each cell to another cell then converting but the form is not consistent. Is
there anyway to programatically convert this elasped time field to a
consistent number format so that each users usage can be subtotaled?

 

[Bernard Liengme]

In A1, I entered this text: 01:20:45:45
And in B1 I used =TIME(LEFT(A1,2),MID(A1,4,2),MID(A1,7,2)+RIGHT(A1,2)/100)
Not withstanding the fact that a Goggle search gave lots of recommendations
to use a custom format of h:mm:ss.000, I was unable to get a working format
to display 1:20:45.450

Here is my workaround. Text in column A, formulas in B and C
01:20:45:48 1:20:45 0.480
02:04:05 2:04:05 0.000
02:06:05:55 2:06:05 0.550


B1:=TIME(LEFT(A1,2),MID(A1,4,2),MID(A1,7,2))
C1:=IF(LEN(A1)>8,RIGHT(A1,2)/100,0)

Sum to nearest second with: =SUM(B1:B3)+SUM(C1:C3)/(24*60*60)
Get the milliseconds with: =MOD(SUM(C1:C3),1)

Hope this helps
best wishes

 

[Kaykayme]

Thanks very much. But I for see a problem because some of the elapsed time
has 3 digits for the hours i.e. "132:17:50:55" and some cells only display
hours and minutes i.e. "0:14". I guess I could find the semicolons and use
an if or select case statement to format. Is there any way to loop through
the each character in each cell and count the number of semicolons in each
cell?

 

[Bernard Liengme]

This finds how many colons: =LEN(A1)-LEN(SUBSTITUTE(A1,":",""))

 

[Kaykayme]

Thanks for the code. However, I ran into an error. The keyword SUBSTITUTE
is not in the VBA language. I used the InStr function with Binary Compare
and this gave me the desired results.

 

[Bernard Liengme]

Glad you found a workaround.
Many Excel functions can be accessed in VBA.
Here is an example
Sub what()
Set mytext = Range("A1")
mylen = Len(mytext) - Len(WorksheetFunction.Substitute(mytext, ":", ""))
MsgBox mylen
End Sub
best wishes

 

[Kaykayme]

Hello, It's me again.
I found that when running my code that the InStr function does not give me
the number of colons but it does give me the position of colon in the string
based on the start position. Using this information I wrote the following to
give me the Time cell in the desired format.

Function FormatTime()
Dim strTimeCell
Dim c
Dim d
Dim e
Dim i
Dim strXCell As String
Dim strVTimeCell As String

d = 1
'First, change the format in destination cell to Text. Then
'copy the text of the Time field to the destination field.

For Each c In Worksheets("2008 User Data (2)").Range("F2:F393")
d = d + 1
strTimeCell = Worksheets("2008 User Data (2)").Range("F" & CStr(d)).Text
strXCell = "H" & d
Worksheets("2008 User Data (2)").Range(strXCell).NumberFormat = "@"
Worksheets("2008 User Data (2)").Range(strXCell).Value = strTimeCell
Next c

'Second, count the number of colons in each Time cell. Then
'add decimal,zeros and/or colon based on the number of colons.
'Format cell in the elapsed time format.

For Each c In Worksheets("2008 User Data (2)").Range("H2:H393")
d = d + 1
i = 1
strVTimeCell = Worksheets("2008 User Data (2)").Range("H" & CStr(d))
strTimeCell = Worksheets("2008 User Data (2)").Range("H" & CStr(d)).Text

Select Case FindColon(d)
Case 1
With Worksheets("2008 User Data (2)").Range("H" & CStr(d))
.Value = strVTimeCell & ":00.0"
.NumberFormat = "[h]:mm:ss.s"
End With
Case 2
With Worksheets("2008 User Data (2)").Range("H" & CStr(d))
.Value = strVTimeCell & ".0"
.NumberFormat = "[h]:mm:ss.s"
End With
Case 3
With Worksheets("2008 User Data (2)").Range("H" & CStr(d))
.Value = Replace(strVTimeCell, ":", _
Left(strVTimeCell, Len(strVTimeCell) - 3) & ".", _
Len(strVTimeCell) - 2, 1)
.NumberFormat = "[h]:mm:ss.s"
End With
End Select
Next c

End Function

Function FindColon(d As Variant)
Dim a
Dim e
Dim f
Dim i

'a = InStr(1, Worksheets("2008 User Data (2)").Range("H1").Text, "T",
vbBinaryCompare)
f = 1
i = 0
e = 1
While i < 3 And e <> 0
e = InStr(f, Worksheets("2008 User Data (2)").Range("H" & CStr(d)),
":", vbTextCompare)
If e > 0 Then
f = e + 1
i = i + 1
End If
Wend
FindColon = i

End Function

This worked as expected. Thanks again for your help.