Working in google sheets, I am getting input from another program in the form of "Xh Ym Zs" to track time. I'm trying to write a formula that will just yield a number of minutes, so "2h 22m 5s" would give 142.

I'm trying all sorts of things with find, search, regexmatch, but I'm running into 2 problems -- one is that there's no set number of characters in each category -- 1h 5m 40s instead of 01h 05m 40s. the other is that the input I'm getting just leaves out a category if it's a zero -- if a student was working for just 35 minutes, the program gives me '35m', not 0h 35m 0s. Similarly, a student who worked for 4 hours and 25 seconds yields '4h 25s'.

Is it even possible to do this with one formula?


1 Answer 1


Use regexextract(), like this:

  get_, lambda(s, ifna(regexextract(A2, "(\d+)" & s), 0)), 
  duration, value(join(":", get_("h"), get_("m"), get_("s"))), 
  iferror(round(24 * 60 * duration)) 

See let(), lambda(), ifna(), regexextract(), value(), join(), iferror(), and round().

For some background, see Working with date and time values in Google Sheets.


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.