0

In a Google sheet, I have a list of rows with a length attached to each:

Example 1 - 1h 30m

Using a formula, I need to convert "1h 30m" to a numerical value. The longest duration I have in the sheet are 4 digit durations (ie 1000h 30m)

Does anyone know how to best do this?

Vendrium
  • 55
  • 6
  • Does [this answer](https://webapps.stackexchange.com/a/143497) help you ? – l -_- l Jun 28 '22 at 10:32
  • I don't think so because the person in that particular thread set up his numbers using duration formatting, whereas I did not. My durations are simply text strings. – Vendrium Jun 28 '22 at 10:34

1 Answers1

2

Assuming the text string 1h 30m is in cell B2, use a regexreplace() formula in another cell, like this:

=to_date( value( regexreplace(B2, "(\d+)h (\d+)m", "$1:$2") ) )

Format the formula cell as Format > Number > Duration or a custom time format of your choosing.

See this answer for an explanation of how date and time values work in spreadsheets.

To learn the exact regular expression syntax used by Google Sheets, see RE2.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • I'm getting a Formula Parse Error for that formula. Am I supposed to substitute anything in that formula (ie "value")? I did edit B2 to reflect the correct text string but the Formula Parse Error remained. – Vendrium Jun 28 '22 at 10:38
  • In the event your spreadsheet locale uses commas `,` as decimal separators, you will have to use semicolons `;` as formula argument separators. Replace the two commas in the formula with semicolons. – doubleunary Jun 28 '22 at 10:44
  • That worked, thank you! quick follow up question - what if I wanted to do a reverse conversion? so e.g. 9:00 -> 9h 00m - how would that work? If you think that warrants a separate post, let me know and I'll make one :) – Vendrium Jun 28 '22 at 13:30
  • It usually makes more sense to use a _custom number format_ to present durations in the format you need. You can then get the data as text strings with `to_text()` if necessary. Please ask only [one question per post](https://meta.stackexchange.com/a/222741). – doubleunary Jun 28 '22 at 14:19