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

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

CodePudding user response：

Try, in J12

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

then apply duration format

CodePudding user response：

try:

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