I want to batch convert duration values in google sheets to decimal format.
The format of data that I'm getting is 2h 30m or 1h 6m which AFAIK is not very standard.
Expected results:
| Source | Result |
|---|---|
| 2h 30m | 2.5 |
| 1h 6m | 1.1 |
I've been trying using formatting options, as well as INDEX and SPLIT formulas but did not managed to achieve expected results
CodePudding user response：
If you have source values in range A2:A10, then try the following formula in cell B2:
=arrayformula(query(regexextract(A2:A10,"(\d )h (\d )m")*1,"select Col1 (Col2/60) label Col1 (Col2/60) ''"))
Look at the following screenshot:
