0

I have a column with data like this:

2 Hour, 23 Minute
1 Hour, 10 Minute

I need a total summation of this column in Hour Minute format. It can be 3 Hour, 43 Minute. Or 3:43 will do.

So far, I have succeeded to convert the text to this format-> 2:23, 1:10

5 Answers 5

2

Do the following:

enter image description here

How it works:

  • Enter 02:33 and 01:10 in cells E22 & E23.

  • Apply this format on both cells:

    [h] "hours ," mm "minutes"

  • Enter this formula in cell E25 & E26:

    =E22+E23

  • Applied format is [h] "hours ," mm "minutes".

  • Format on cell E26 is h:mm.

Adjust cell references in the formula as needed.

2
  • This is the best answer.............attack the issue at the data-entry stage rather than afterwards *(+1) Sep 16, 2020 at 13:19
  • Thanks @Gary'sStudent ,,, these words pumps lots of positive and encourage me to do better ☺ Sep 17, 2020 at 4:46
2

With data in A1 through A5, in B1 enter:

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1," Hour, ",":")," Minute",":00"))

and copy downwards. Then apply a proper format to column B:

enter image description here

Finally, in another cell enter:

=SUM(B:B)

enter image description here

1
  • works fine when total is less than 24hrs. For 24hrs and more it is starting for 0 again
    – BlackCat
    Sep 17, 2020 at 4:04
1

Use a VBA function (Set variables: a with column name, j with start row, k with end row:

Function Calc()
  Dim c As String
  Dim j As Integer
  Dim k As Integer
  Dim n As Integer
  Dim res As Integer
  c = "A"
  res = 0
  j = 1
  k = 8
  Dim varCell As Variant
  Dim testSplit,testWord
  For i = j To k 
    varCell = Range(c & i).Value
    testSplit = Split(varCell,",")
    For Each o In testSplit
      testWord = Split(o," ")
      Select Case UBound(testWord)
        Case "Second"
          n = testWord(0)/60
          res = res + n
        Case "Minute"
          n = testWord(0)
          res = res + n
        Case "Hour"
          n = testWord(0)*60
          res = res + n
      End Select
    Next
  Next
res = res/60
End Function

Here res is the result.

2
  • Nice solution. The OCD in me though would point you to the best-practice pattern for variable names. Use Hungarian notation, and define a string as Dim strName, an integer as Dim intValue etc... It doesn't matter so much for a small module with private variables, but it's a good habit to get into. Sep 16, 2020 at 13:08
  • Thank you @spikey_richie for the good pointer
    – Wasif
    Sep 16, 2020 at 14:16
1

You need to convert from text a human can read to numbers Excel can sum and then back into text a human can read. It'll end up looking something like this:

shot2


You said you've already converted the text to a h:mm format. I don't know what you used to do that but, for future readers that didn't figure out that step, here's one solution:

=SUBSTITUTE(SUBSTITUTE(A1," Hour, ",":")," Minute","")

Of course, that only works if all the times are input exactly as they are in your question. If someone was less confident about the input data, they could break it up into pieces with something like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Hour",":"),"Minute","")," ",""),",","")

Now, though, we need Excel to recognize the string 2:23 as a time value. Luckily, there's a function that does exactly that.

=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A1," Hour, ",":")," Minute",""))

That will, by default, return 0.099306 for an input of 2:23 because Excel is counting days. 2 hours and 23 minutes is 9.9306% of a whole day. However, 1:00 will give the same result as 25:00 because TIMEVALUE() only extracts, well, the time value. We'll add in the date value, too.

A1 : 2 Hour, 23 minute
B1 : =SUBSTITUTE(SUBSTITUTE(A1," Hour, ",":")," Minute","")
C1 : =DATEVALUE(B1)+TIMEVALUE(B1)

Copy the formulas in B1 and C1 down as needed to convert all your inputs to a number. Somewhere else, add them all up.

D1 : =SUM(C:C)
D2 : =INT(D1)*24+VALUE(TEXT(D1,"h")) & ":" & RIGHT(TEXT(D1,"h:mm"),2)

The first half of that pulls out the hours value. INT(D1) gives you the integer part which will be however many whole days there are. Multiply by 24 to get the hours. VALUE(TEXT(D1,"h")) converts the number to text formatted to only show the hours component and then converts that text to a number so it can be added to the number of days*24. RIGHT(TEXT(D1,"h:mm"),2) converts the number to text showing only the hours and minutes and then extracts just the last two characters which will just be the minutes.

This final piece can be edited however you like to change the display. For instance:

=INT(D1)*24+VALUE(TEXT(D1,"h")) & " Hours, " & RIGHT(TEXT(D1,"h:mm"),2) & " Minutes"

shot2

shot3

You can also use date formats to display hour values greater than 24. For instance, a cell value of 1.48125 with a custom number format of [h]:mm will display 35:33. That can be modified in a similar manner to display different text: [h]" Hours, "mm" Minutes". Key Point: This keeps the underlying value as a number so you can do further maths on it.

0

If your format is as you show it:

<hours><space><text><minutes><space><text>`

And if you have Windows Excel 2010+, you can use this formula:

someCell: =(SUM(FILTERXML("<t><s>" & SUBSTITUTE(TRIM(A1:A6)," ","</s><s>") & "</s></t>","//s[1]"))*60+
SUM(FILTERXML("<t><s>" & SUBSTITUTE(TRIM(A1:A6)," ","</s><s>") & "</s></t>","//s[3]")))/1440

Custom format someCell as [h]:mm

  • Extract the hours and multiply by 60
  • Add the minutes
  • Divide the sum of the above by 1440 (number of minutes in a day)
  • format the result to show hours:minutes

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .