I've got an issue with some time data that is exported from a program like this into cells (each number with text in each cell descending all in column a)





25h 30m


8h 30m


I need to somehow have this converted in to decimal time 25.5 the main problem is that it has text it the cells (h and m) which I cant remove to even start converting it. Any ideas the best way to solve this?

3 Answers 3


enter image description here

to solve even the cases with spaced m and h, such as those in the attached image, you could try this other calculation:

=ARRAYFORMULA(if(A2:A="","",iferror(REGEXEXTRACT(A2:A,"(\d+\s?)h"),"") + iferror(Round(REGEXEXTRACT(A2:A,"(\d+\s?)m")/60,2),"")))
  • amazing, it worked, thank you so much!!
    – R B
    Commented Jan 27, 2022 at 12:06

you can try for not integer:

=VALUE(REGEXREPLACE(A11; "(.+)+h ?(.+)m"; ("$2")))/60 + VALUE(REGEXREPLACE(A11; "(.+)h ?(.+)?m?"; ("$1")))

and for integer:

=VALUE(REGEXREPLACE(A9; "(.+)h ?(.+)?m?"; ("$1")))

I cant get that together

  • I've got something similar to this using this =IF(ISNUMBER(FIND("m",Q3)),SUBSTITUTE(SUBSTITUTE(Q3,"h ",":"),"m",":0"),SUBSTITUTE(Q3, "h",":0:0"))*24 now however i dont know how to make array if you know. Thanks for commenting and helping me
    – R B
    Commented Jan 26, 2022 at 9:46

Suppose your Col-A data begins in A2 (with a header, say, in A1).

Make sure Col-B is empty (or some other column) and then place the following formula in cell B1 (or the top cell of some other open column):

=ArrayFormula({"Converted"; IF(A2:A="",, REGEXEXTRACT(A2:A,"\d+") + REGEXEXTRACT(A2:A&"0m","(\d+)m")/60)})

This one formula will created the header (which you can change as you like within the formula itself) and all conversions for non-null cells in Col A.

  • Thanks Erik, I've used your formula however it seems to miss out the minute side of things so 25h 30m formats to 25 instead of 25.5 I have managed to get this working however its not in an array format which would be preferable =IF(ISNUMBER(FIND("m",Q3)),SUBSTITUTE(SUBSTITUTE(Q3,"h ",":"),"m",":0"),SUBSTITUTE(Q3, "h",":0:0"))*24
    – R B
    Commented Jan 26, 2022 at 9:44
  • My formula works fine in my own sheet with your data. It's likely that you just had the results range set to the wrong format (such as 0) instead of 0.00. If you'd like to share a link to a sample spreadsheet, I can take a look. But as I say, the formula I provided worked to produce all expected results in my own sheet.
    – Erik Tyler
    Commented Jan 26, 2022 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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