0

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)

0h

14h

17h

17h

25h 30m

0h

8h 30m

7h

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?

Improve this question

3 Answers 3

Reset to default
0

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),"")))
Improve this answer
1
  • amazing, it worked, thank you so much!!
    – R B
    Jan 27, 2022 at 12:06
0

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

Improve this answer
1
  • 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
    Jan 26, 2022 at 9:46
0

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.

Improve this answer
2
  • 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
    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
    Jan 26, 2022 at 12:29

Your Answer

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

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