Text value ('## Minutes') to hours.

[GR00007]

I have a formula that works, but was wondering if there's a more efficient way of doing this.
The challenge: Trying to parse values like "30 Minutes" and "2 Hours" and "1.5" to a time value.
Formula that is working:
=IF(TYPE(E2)=1,E2,IF(IFERROR(FIND("MINU",UPPER(E2)),0) > 0,VALUE(LEFT(E2,FIND("MINU",UPPER(E2)) -1)) / 60,IF(IFERROR(FIND("HOUR",UPPER(E2)),0) > 0,VALUE(LEFT(E2,FIND("HOUR",UPPER(E2)) -1)),E2)))

This is kinda cludgy - looking for a more elegant way.

 

[joeu2004]

=IFERROR(VALUE(LEFT(E2,SEARCH("m",E2)-1))/60, IFERROR(VALUE(LEFT(E2,SEARCH("h",E2)-1)), E2))

That allows for "m", "min", "minute" as well as "minutes" and "h", "hr", "hour" as well as "hours".

It is case-insensitive (SEARCH instead of FIND).

Testing for ISNUMBER(E2) first might be more effiicient, but generally, it is superfluous. It is covered by the failure of both IFERROR conditions.

Usually, I would write --LEFT(...) instead of VALUE(LEFT(...)). But IIRC, there are circumstances where VALUE(string) is more forgiving of spaces than --"string". Hmm, but not according to my latest testing. Oh well, your choice.

BTW, this returns decimal hours, like your original formula, not what I would call a "time value". IMHO, the latter term refers to Excel time, which is of the form h:m:s .

PS.... That formula returns zero if E2 is empty (no value), like your original formula.

 

[GR00007]

Thank you for your reply - much appreciated. Using Search instead of Find is definitely the way to go, I'm a bit hesitant to use "m" to indicate minute just in case somebody used "month" (I doubt millennium would be used), and I'd want "month" to remain [I'll check for "min"]. For Hours "h" should suffice, I can't think of any other common time text. Yes, IFERROR would cover for unexpected text - wouldn't checking first for numeric be a bit more efficient since the string manipulation would bypassed? I apologize for saying 'time value', you are quite correct I wanted decimal.

 

[joeu2004]

wouldn't checking first for numeric be a bit more efficient since the string manipulation would bypassed?

.... Which is why I wrote: "Testing for ISNUMBER(E2) first might be more effiicient". If you are asking how to do that....

=IF(ISNUMBER(E2), E2, IFERROR(VALUE(LEFT(E2,SEARCH("m",E2)-1))/60, IFERROR(VALUE(LEFT(E2,SEARCH("h",E2)-1)), E2)))

Whether it is more or less efficient depends on the likelihood of having just numeric input and how many times you will have a formula of that form.

You can make your test for "minute" or "hour" as restrictive as you like. An afterthought: It might be better to test for " m" and " h" (leading space), if you want to avoid misinterpreting interstitial "m" and "h". But that does presume that you want to disallow (not recognize) something like 123minutes .

 

[GR00007]

I just think being efficient is preferable unless it adds too much complexity.
IFERROR(VALUE(LEFT(E2,SEARCH("min",E2)-1))/60,E2) will ignore a trailing space of "123 Minutes" while still picking up "123minutes", both returning 2.05; while "123 Months" will hit the error and remain as "123 Months"; anything other than a leading text portion of "m" or "h" would cause the result to be caught by the corresponding IFERROR which is what is desired.
I would like to say thank you again for the quick response and better formula than the one I originally posted.