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:
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"
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.