Hello Everyone,
I finally got it to work! I wrote a function that counts the number of
colons in the time cell. On the cells that have time formats with one or two
colons, I just changed the format in the destination cell to [h]:mm:ss.00 and
copied the time value. But on the cells that had three colons changed the
format in the destination cell to [h]:mm:ss.00 and added a formula to the
source cell that substituted the third colon for a decimal. Then created a
variable for the text in the source cell and added a formula which changed
the text to a value in the destination cell. An example is below:
For Each c In Worksheets("User Data (2)").Range("H2:H393")
d = d + 1
strVTimeCell = Worksheets("User Data (2)").Range("F" & CStr(d))
Select Case FindColon(d)
Case 1
With Worksheets("User Data (2)").Range("J" & CStr(d))
.Value = strVTimeCell
.NumberFormat = "[h]:mm:ss.00"
End With
Case 2
With Worksheets("User Data (2)").Range("J" & CStr(d))
.Value = strVTimeCell
.NumberFormat = "[h]:mm:ss.00"
End With
Case 3
Worksheets("User Data (2)").Range("H" & CStr(d)).NumberFormat =
"[h]:mm:ss.00"
Worksheets("User Data (2)").Range("H" & CStr(d)).Formula =
"=SUBSTITUTE(F" & d & "," & _
"""" & ":" & """" & "," & """" & "." & """" & ",3)"
strTime = Worksheets("User Data (2)").Range("H" & CStr(d)).Value
With Worksheets("User Data (2)").Range("J" & CStr(d))
.NumberFormat = "[h]:mm:ss.00"
.Formula = "=VALUE(" & """" & strTime & """" & ")"
End With
End Select
Next c
Kaykayme said:
Thank you all for your help. What seems to be the problem is the original
format was different for each row (Ex. 37:10:46:54, 23:11, 6:26:54) So I
formatted a column as text and using vba copied the text of each cell into
the text formatted column so that they all were uniform in format (I added
0's and colons as needed) then changed the format to [h]:mm:ss.00. However,
the numbers values show as text and not as numbers or elasped time. This
data was parsed from a spreadsheet derived from html. So when the elapsed
time includes milliseconds it shows as text. I need a formula to convert
the text to a number that would represent the elasped time including
milliseconds.
Satti Charvak said:
You can use the below formula to calculate into seconds and do the sum total
and finally convert the data to hours, minutes and seconds
=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)
--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India
Kaykayme said:
I have 392 rows of accumulated hours to calculate. I have tried using the
sum function but it only gives 0. However, when I add individual cells I get
a total but it is not correct.
Example:
A1 = 37:10:46.54 and A2 = 23:11:00.00
Formated: [h]:mm:ss.00
Formula: =A1+A2.
I tried using a different time format [h]:mm:ss but I still get 0.
Is there a limit on rows to calculate?
