google-sheetslambdatimegoogle-sheets-formuladuration

How can I convert a duration in minutes/seconds in google sheets

I have a google sheet column which has the duration column in the format as like the below. 4w 1d 19h 56m 16s. How can I convert this in total minutes/seconds?

PS: Every value in that list will be optional. Meaning, Sometime Weeks will be missing and days will be missing etc.

Solution

  • total minutes/seconds

    =ARRAYFORMULA(IF(A1:A10="",,TEXT(MMULT(IFERROR(REGEXEXTRACT(SPLIT(A1:A10, " "), "\d+")*
 VLOOKUP(REGEXEXTRACT(SPLIT(A1:A10, " "), "\d+(.*)"), 
 {"s",1;"m",60;"h",3600;"d",86400;"w",604800}, 2, 0), 0), 
 SEQUENCE(COLUMNS(SPLIT(A1:A10, " ")), 1, 1, 0))/86400, "[mm]:ss")))

    enter image description here

    update

    improved version:

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

    enter image description here