# Sum of time problem

#### JaneMore

##### New Member
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>

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Gerald Higgins

##### Well-known Member
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

##### Well-known Member
Hi,

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

Excel 2016 (Windows) 64 bit
AB
10m 0s0:00
20m 0s0:00
30m 34s0:34
411m 34s11:34
50m 0s0:00
60m 43s0:43
76m 53s6:53
80m 33s0:33
92m 1s2:01
100m 29s0:29
110m 0s0:00
123m 11s3:11
130m 0s0:00
146m 58s6:58
1514m 7s14:07
163m 58s3:58
1713m 38s13:38
189m 24s9:24
1911m 34s11:34
2085:37
Sheet1
Cell Formulas
RangeFormula
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"))

Last edited:

#### Dr. Demento

##### Well-known Member
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:

Code:
``````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:
Code:
``````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``````

Last edited:

Replies
16
Views
2K
Replies
10
Views
2K
Replies
3
Views
261
Replies
6
Views
11K
Replies
0
Views
386

1,191,118
Messages
5,984,749
Members
439,907
Latest member
Kayfabe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back