Convert Cumulative Time / Time Duration to Text or String

[bemp87]

I have a Cumulative Time field that formats as [h]:mm:ss that aggregates time in hours. I am needing to extract the time portion to convert it to a Text or String value. For example, a cumulative time total of 95:31:00 shows as "1/3/1900 11:31:00 PM" in the formula bar which is not what i want. The value 95:31:00 is what i need to load into an MSFT Access database, but it does not accept cumulative totals, so i've been having to convert it manually which is coming extremely time consuming by using the following formula: ="95:31:00". This gets almost impossible when I am working with many rows.

I have tried the TRUNC() function for this and cannot seem to get this to work. Any help would be appreciated.

 

[shaowu459]

TEXT will help you convert the time to a string. Copy column B and paste value to any column.

Book5
	A	B	C
1	95:31:00	95:31:00	95:31:00
2	96:31:00	96:31:00	96:31:00
3	97:31:00	97:31:00	97:31:00
4	98:31:00	98:31:00	98:31:00
5	99:31:00	99:31:00	99:31:00
6	100:31:00	100:31:00	100:31:00
7	101:31:00	101:31:00	101:31:00
8	102:31:00	102:31:00	102:31:00
9	103:31:00	103:31:00	103:31:00
10	104:31:00	104:31:00	104:31:00
11	105:31:00	105:31:00	105:31:00
12	106:31:00	106:31:00	106:31:00
Sheet1

Cell Formulas
Range	Formula
B1:B12	B1	=TEXT(A1,"[hh]:mm:ss")

 

[bemp87]

Wow. I'm annoyed with myself, I tried this exact thing but left off the square brackets around hh that resulted in the wrong value. This works perfectly and solves a week long headache and has just saved me an insurmountable amount of work. Thank you!!!

 

[shaowu459]

Wow. I'm annoyed with myself, I tried this exact thing but left off the square brackets around hh that resulted in the wrong value. This works perfectly and solves a week long headache and has just saved me an insurmountable amount of work. Thank you!!!

Glad to know you solved the problem

 

[bemp87]

I'm Curious, is this possible to do programmitcally using vba? I have the following code but it keeps inserting the value as a datetime format instead of a string? The Timestring is not returning as an actual string value capturing the values as "[hh]:mm:ss". What am i Doing Wrong?

Sub DataCleanse()
    Dim RowNum As Long
    Dim CpH As Double
    Dim StatDate As Date
    Dim EomFlag As Integer
    Dim TimeString As String
  
  
    StatDate = "05/22/2020"
    EomFlag = 0
    RowNum = 2
    For RowNum = 2 To 19
        CpH = Round(Cells(RowNum, 13).Value / (Cells(RowNum, 11).Value * 24), 2)
        TimeString = WorksheetFunction.Text(Cells(RowNum, 11), "[h]:mm:ss")
        Cells(RowNum, 15).Value = CpH
        Cells(RowNum, 16).Value = StatDate
        Cells(RowNum, 17).Value = EomFlag
    Next RowNum
End Sub