Calculating hr/min format

[Lee Ann]

I have a column where the time is listed in an hr/minute format (example: 11
h 30 min). There are a number of rows with this information and I need to
total the time used (with the format given) and am unsure how to accomplish
this. I'm hoping there would be a simple formula.

Thanks in advance.

 

[Trev B]

Hi Lee Ann

What you are asking is not so easy in the format shown.

Suggestion in your excel sheet why not try the following.

1. Make column showing hours in figures
2. Make column showning mins in figures
3. Make a column showing ( (1*60 + 2) ) this will give you mins
4. Make a cell a total of 3 to give total mins
4. Then convert the mins to Hours and mins for answer

 

[Luke M]

You can use this array** formula. Adjust range size as needed:

=SUM(TIME(VALUE(LEFT(A2:A200,FIND("
",A2:A200)-1)),VALUE(MID(A2:A200,FIND("h",A2:A200)+2,2)),0))

Note that this assumes input never gives a 3 digit value for minutes.
**Use Ctrl+Shift+Enter to confirm formula, not just Enter.

 

[Lee Ann]

Thanks for the responses. I started reading through some of the other posts
(which I probably should have done before asking for a solution). It seems
to accomplish the same thing if I change the format from 1 h 30 min to 1:30
and then add the string where I'm looking for the totals with a simple
=sum(A1:A200). Correct?

 

[Luke M]

Possibly. If the data is literally "1 h 30 min" then you can't change the
formatting of the cell to "1:30". However, if someone is using a custom
format of (under format - cell, number tab, custom)
h" h "mm" min"
Then yes, you can simply change the format and use SUM.

In short, if the data is inputed as a number, and formatted to display text,
you're good to go (don't even need to change formatting). If the data is
text, you'll need to use a formula to manipulate it back into numbers.

Also of note, here's a shorter array formula that converts your text into
numbers and sums:

=SUM(TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A2:A100," h ",":")," min","")))

 

[Lee Ann]

I did try Trev B's suggestion and that worked. I don't believe the
"administration" ever planned on using this information in this manner,
otherwise it would have been formatted differently, as this took some time to
reformat. But, it did work in the end.

Thanks again!

 

[Trev B]

Thanks Lee Ann,

Somestimes KISS is better because it is easier to understand especally when
you look back at it after a period of time.

I glad it worked and of course it would have been better if formatted
correctly in the first place. This just proves that higensight is never
wrong.

Regards