3

I have decimal values followed by a letter to indicate the time unit (hours/minutes) values like the following:

  • 60m
  • 1.5h
  • 15m
  • 20h

How can I convert these to a numeric value of minutes or time value? So in other words the above cells would become:

  • 60
  • 90
  • 15
  • 1200

I have found the following formula from here: https://www.reddit.com/r/excel/comments/o0bxvl/formula_to_convert_time_in_text_like_2h_15m_or/

=IF(ISERR(FIND(" ",B2)), LEFT(B2,FIND("m",B2)-1)/(60*24), LEFT(B2,FIND("h",B2)-1)/24+MID(B2,FIND(" ",B2)+1,FIND("m",B2)-FIND(" ",B2)-1)/(24*60))

However that does not seem to work in my case - I assume because I have values like 1h instead of 1h 0m...


To solve this I have been trying to use the REGEX function but I'm giving up as it's taking too long to figure this out, if it's helpful to anyone in future I was partially trying a regex/function as: =(REGEX(E2,"(\d+)m|.*","$1",""))

2 Answers 2

3

Ugly but working:

=IF(ISERR(FIND("h";A1));VALUE(LEFT(A1;FIND("m";A1) -1));VALUE(LEFT(A1;FIND("h";A1)-1))*60)

Split on mutiple lines for readability:

=IF(
    ISERR(
        FIND("h";A1)
    );
    VALUE(
        LEFT(A1;FIND("m";A1)-1)
    );
    VALUE(
        LEFT(A1;FIND("h";A1)-1)
    )*60
)

Explanation:

  • check if we have minutes or hours (using ISERR on the result of FIND());
  • if we have minutes: just take the numerical value of cell content, stripping the last character.
  • if we have hours: take the value of the cell content, stripping the last character (should yield the numerical content), multiply by 60;

I'm sure there are more elegant ways to solve this...

0

=IFERROR(VALUE(LEFT(A1,LEN(A1)-1))*60^(FIND(RIGHT(A1,1),"smh")-1),A1)
... creates the correct seconds count from h, m, and s suffixes.

... and similarly:
=IFERROR(VALUE(LEFT(A1,LEN(A1)-1))*60^(FIND(RIGHT(A1,1),"smh")-2),A1)
creates a correct minutes count from h, m and s suffixes.

Due to IFERROR(...,A1) the content in A1 is kept unchanged for pure numbers or other 'last' characters.
You might need an 'Error indicator' instead though,
e.g. replace A1 here with STR(A1)&" has no h/m/s indication."

How it works:
FIND(RIGHT(...)...) converts the rightmost char in A1 to an "index"
-1 or -2 adjusts it for use in...
60^"index" - which forms a constant to use in the multiplication with
VALUE(LEFT(A1,... - the value before the suffix.

Note here that, in the formula to create 'minutes'
60^n is equal to
1/60 for n=-1 thus forming then constant to multiply 'seconds' to be 'minutes'
1 for n=0 for keeping 'minutes' as is
60 for n=1, allowing conversion of hours to minutes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.