Sum of time problem

[JaneMore]

I have a excel row of time values how can I auto sum these as each time I try I get 0 back, need to convert the sum in to hours and minutes

0m 0s

0m 0s

0m 34s

11m 34s

0m 0s

0m 43s

6m 53s

0m 33s

2m 1s

0m 29s

0m 0s

3m 11s

0m 0s

6m 58s

14m 7s

3m 58s

13m 38s

9m 24s

11m 34s

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

 

[Gerald Higgins]

Wow this is vague !

Is your data text strings, or numbers (which you have formatted to appear as times) ?

For your SUM function, have you tried formating the result as a time format ?

 

[jorismoerings]

Hi,

take a look at this and see if this helps you:

Excel 2016 (Windows) 64 bit
	A	B
1	0m 0s	0:00
2	0m 0s	0:00
3	0m 34s	0:34
4	11m 34s	11:34
5	0m 0s	0:00
6	0m 43s	0:43
7	6m 53s	6:53
8	0m 33s	0:33
9	2m 1s	2:01
10	0m 29s	0:29
11	0m 0s	0:00
12	3m 11s	3:11
13	0m 0s	0:00
14	6m 58s	6:58
15	14m 7s	14:07
16	3m 58s	3:58
17	13m 38s	13:38
18	9m 24s	9:24
19	11m 34s	11:34
20		85:37
Sheet1

Cell Formulas
Range	Formula
B20	=SUM(B1:B19)
B1	=TIMEVALUE(TEXT(LEFT(A1,FIND("m",A1,1)-1),"00")&":"&TEXT(MID(A1,FIND("m",A1)+2,FIND("s",A1,1)-(FIND("m",A1,1)+2)),"00"))

 

[Dr. Demento]

If I understood your request correctly . . .

Assuming each time entry in a single cell and all cells are in single column (this will not work with multiple columns), this should work:

Sub time_wordparse()
Dim rng As Range, _
    rngUsed As Range
  Set rngUsed = Application.InputBox("Select range", , , , , , , 8)
Dim sht As Worksheet
    Set sht = rngUsed.Parent
    
Dim lng_h() As Long, _
    lng_m() As Long, _
    lng_s() As Long
Dim rowcnt As Long
  rowcnt = rngUsed.Rows.count
Dim i As Long, _
    j As Long, _
    total As Long
    
Dim str As String
Dim strTemp() As String
 ReDim lng_h(1 To rowcnt)
 ReDim lng_m(1 To rowcnt)
 ReDim lng_s(1 To rowcnt)
 On Error Resume Next
    
  For j = 1 To rowcnt
    strTemp = Split(rngUsed.Cells(j, 1).value, " ")
    For i = LBound(strTemp) To UBound(strTemp)
      Select Case Right(strTemp(i), 1)
        Case "h"
          lng_h(j) = Left(strTemp(i), Len(strTemp(i)) - 1)
        Case "m"
          lng_m(j) = Left(strTemp(i), Len(strTemp(i)) - 1)
        Case "s"
          lng_s(j) = Left(strTemp(i), Len(strTemp(i)) - 1)
      End Select
    Next i
    
    total = total + (lng_h(j) * 3600 + lng_m(j) * 60 + lng_s(j))
    Debug.Print j & " | " & lng_h(j) & " | " & lng_m(j) & " | " & lng_s(j)
  Next j
On Error GoTo 0
  sht.Range("E1:H1").value = Array("hours", "minutes", "seconds", "total time")
  sht.Range("E2:E" & UBound(lng_h) + 1).value = WorksheetFunction.Transpose(lng_h)
  sht.Range("F2:F" & UBound(lng_m) + 1).value = WorksheetFunction.Transpose(lng_m)
  sht.Range("G2:G" & UBound(lng_s) + 1).value = WorksheetFunction.Transpose(lng_s)
  sht.Range("H2:H2").value = time_convert(total)
  
End Sub

Where time_convert is:

Public Function time_convert(Seconds As Long, _
                             Optional Verbose As Boolean = False) As String
' [URL="http://freevbcode.com/ShowCode.asp?ID=85"]FreeVBCode code snippet: Convert seconds to Hours, Minutes, and Seconds[/URL]
' ~~ If verbose = false, returns: 02:22.08 _
     If true, returns: 2 hours, 22 minutes, and 8 seconds
Dim lngHours As Long, _
    lngMinutes As Long, _
    lngSeconds As Long
Dim str As String
lngSeconds = Seconds
lngHours = Int(lngSeconds / 3600)
lngMinutes = (Int(lngSeconds / 60)) - (lngHours * 60)
lngSeconds = Int(lngSeconds Mod 60)
  
  If lngSeconds = 60 Then
    lngMinutes = lngMinutes + 1
    lngSeconds = 0
  End If
  
  If lngMinutes = 60 Then
    lngMinutes = 0
    lngHours = lngHours + 1
  End If
  
  str = Format(CStr(lngHours), "#####0") & ":" & _
        Format(CStr(lngMinutes), "00") & ":" & _
        Format(CStr(lngSeconds), "00")
  
  If Verbose Then str = TimeStringtoEnglish(str)
  time_convert = str
End Function