Convert to time

[Laila Dee]

I have a report that gives me time as 40h 2m. How can I convert this to a time that show as 40:02?

[Jonmo1]

Try

=(LEFT(A1,FIND("h",A1)-1)&":"&TEXT(TRIM(MID(A1,LEN(A1)-2,2)),"00"))

That will return it as a TEXT string 40:02, not a true time value.

If you want a true time value, add 0 to the very end and format the cell with the formula as [hh]:mm
=(LEFT(A1,FIND("h",A1)-1)&":"&TEXT(TRIM(MID(A1,LEN(A1)-2,2)),"00"))+0

[Laila Dee]

Perfect!!! Thank you so much for the quick response.

[daddylonglegs]

Try this formula assuming your data in A2

=SUBSTITUTE(SUBSTITUTE(A2,"h ",":"),"m","")+0

format result cell as [h]:mm

[Laila Dee]

Great, thanks!! That worked too.

[Jonmo1]

You're welcome.