Sum time in format 1h 30m + 2h 40m in excel

Question

I am getting some time spend formats from a table. I would like to add them to an excel sheet and take the sum of them.

The time format is

1h 30m

and

2h 45m

And the sum which I need is

4h 15m

Are there any formulas for this by default? If no, can somebody please help me with this?

Answer 1

Use a helper column and a custom format :

Answer 2

You can easily do this with a user defined function. The following graphic shows an example close to yours in the top two cells and the formula =sumhm(a1:a2) in the bottom cell:

The code to do this is relatively simple:

Option Explicit

Function sumhm(rng As Range)
    Dim pos As Integer
    Dim cell As Range
    Dim str As String
    Dim minutes As Long

    ' Process each cell in the range '

    minutes = 0
    For Each cell In rng
        ' Get string from cell, work out minutes, add to tally '

        str = cell.Text
        pos = InStr(1, str, "h")
        If pos > 0 Then
            minutes = minutes + 60 * CInt(Left(str, pos - 1))
            str = Mid(str, pos + 1)
        End If

        pos = InStr(1, str, "m")
        If pos > 0 Then
            minutes = minutes + CInt(Left(str, pos - 1))
        End If
    Next

    ' All done, revert to XhYm format '

    sumhm = CStr(Int(minutes / 60)) & "h" & CStr(minutes Mod 60) & "m"
End Function

The good thing about using a UDF is that you can get it arbitrarily complex ranges (like C5:D9,G9:H16,B14:D18) and it will just work.

It also centralises it so there's one place where you can fix things like users making your life hell by putting spaces into their strings. Or any bugs you may find (or enhancements you may want, such as also handling things like 3y7w2d9h33m) in my implementation.

Answer 3

Try this:

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",":"),"h",""),"m",""))

The time need to be parsed out to the raw number... This can be done with left() & mid(), but I think the nested Substitute() calls make it more readable.

From the inside out,
1. Change the " " to a colon, then pass that result to the next level
2. Change the "h" to "" (i.e. remove the "h") pass the result to the next level
3. Change the "m" to "" (i.e. remove the "m") pass the result to the next level
4. the result is a text value which needs to be converted to a TIMEVALUE.

You'll want to format the time cells at hh:mm,

The total is a sum() of the cells above.

Good Luck.