Sum of time problem


New Member
Apr 30, 2016
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>

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
Mar 26, 2007
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 ?
Upvote 0


Well-known Member
Jul 4, 2014
Office Version
  1. 365
  2. 2016
  3. 2013
  1. Windows

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

Excel 2016 (Windows) 64 bit
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
Cell Formulas
Last edited:
Upvote 0

Dr. Demento

Well-known Member
Nov 2, 2010
Office Version
  1. 2019
  2. 2016
  1. Windows
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=""]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:
Upvote 0

Forum statistics

Latest member

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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