# How can I convert time values with a letter suffix like (30m, 2h, 30s) into an actual time format?

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",""))`

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

`=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.