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

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.

CodePudding user response：

If the column values are simple strings and you're using Apps Script, you may be able to do something like this:

function transformDurationString(dString) {
  const multipliers = {
    s: 1,
    m: 60,
    h: 60 * 60,
    d: 60 * 60 * 24,
    w: 60 * 60 * 24 * 7,
  };

  let totalSeconds = 0;
  let durations = dString.split(" ");
  
  for (const duration of durations) {
    const durationValue = Number(duration.slice(0, -1));
    const durationType = duration.slice(-1);

    totalSeconds  = durationValue * multipliers[durationType];
  }

  const totalMinutes = Math.floor(totalSeconds / 60);
  const remainderSeconds = totalSeconds % 60;

  return `${totalMinutes}m ${totalSeconds}s`;
}

CodePudding user response：

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")))