Need help converting text to time in duration format on Google Sheets

Question

Asking for some help here, im trying to convert text to time in duration format on Google Sheets, i´ve used some basic fuctions to to breakdown text (with delimiters as d (days) h (hour) m(minute) and s(second) into values that were then baked into a time function, however for outputs over 24 hours I was unable to get it to format properly i.e. in the image below 375 hrs should show 375:00:00 or [H]:mm:ss

Any ideas here?

Sharing the doc

https://docs.google.com/spreadsheets/d/1YWHM5tPaLOulHMbfdR8CZJsER7LBceWLQrm9f8JcV9c/edit#gid=0

Answer 1

Try, in J12

=(G12+H12/60+I12/60/60)/24

then apply duration format

Answer 2

try:

=FLATTEN(INDEX(QUERY(, "select  "&TEXTJOIN(",", 1, 
 SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B7:B27, 
 "d", "*86400"), "h", "*3600"), "m", "*60"), "s", "*1"), " ", "+")))/86400, 2))

Answer 3

Use regexextract(), like this:

=arrayformula( 
  iferror(regexextract(B7:B, "(\d+)\s*h"), 0) & ":" & 
  iferror(regexextract(B7:B, "(\d+)\s*m"), 0) & ":" & 
  iferror(regexextract(B7:B, "(\d+)\s*s"), 0) 
)

The formula gives text strings. To get numeric durations, use value(), like this:

=arrayformula( 
  iferror( 1 / 
    value( 
      iferror(regexextract(B7:B, "(\d+)\s*h"), 0) & ":" & 
      iferror(regexextract(B7:B, "(\d+)\s*m"), 0) & ":" & 
      iferror(regexextract(B7:B, "(\d+)\s*s"), 0) 
    ) 
  ^ -1 ) 
)

Format the result column as Format > Number > Duration.

In the event there are many components in the text string you are converting, it may be beneficial to use just one regextextract(), which is possible using the recently introduced lambda functions:

=arrayformula( 
  map( 
    B7:B, 
    lambda( 
      duration, 
      if( 
        len(duration), 
        join( 
          ":", 
          iferror( 
            regexextract( 
              duration, 
              { "(\d+)\s*h"; "(\d+)\s*m"; "(\d+)\s*s" } 
            ), 
            0 
          ) 
        ), 
        iferror(1/0) 
      ) 
    ) 
  ) 
)

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