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

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

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

Solution

try:

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

update

improved version:

=INDEX(IFERROR(1/(1/BYROW(B3:B15, LAMBDA(i, LET(x, "(\d+)\s*", SUM(IFERROR(
 REGEXEXTRACT(i, x&{"d"; "h"; "m"; "s"}), 0)*{86400; 3600; 60; 1})/86400))))))