Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time milliseconds format
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time milliseconds format
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time milliseconds format
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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time milliseconds format
This finds how many colons: =LEN(A1)-LEN(SUBSTITUTE(A1,":",""))
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time milliseconds format
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" wrote: This finds how many colons: =LEN(A1)-LEN(SUBSTITUTE(A1,":","")) -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time milliseconds format
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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" wrote: This finds how many colons: =LEN(A1)-LEN(SUBSTITUTE(A1,":","")) -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time milliseconds format
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. "Kaykayme" wrote: 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" wrote: This finds how many colons: =LEN(A1)-LEN(SUBSTITUTE(A1,":","")) -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Kaykayme" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding and Subtracting a Time with MilliSeconds | Excel Worksheet Functions | |||
Comparing time values which have milliseconds in them e.g 10:20:30 | Excel Discussion (Misc queries) | |||
Can time be measured to milliseconds? | Excel Programming | |||
Grab Time with milliseconds included in VBA | Excel Programming | |||
Userform stopwatch format milliseconds | Excel Programming |