Search code examples
google-sheetstimeformatduration

Dynamically format duration in google sheets

I want to input times that would be displayed in the formats below, depending on whether the digits are needed or not, like this.

s.mss
ss.mss
m:ss.mss
mm:ss.mss

My main problem is being able to use them to do math with (sum and subtraction only).

  • How could I do that?

Look at the Demo Sheet, i guess

EDIT: I found a better solution! although its more specific to my use case, so the responses might help you more. But I found that inputing the durations as plain text (as you aren't constrained by a single format) and then creating two functions in a custom script (one to convert text to number, the other the reverse) works much better, as I'm free to use them as numbers or text when needed.

// [hh:][mm:][s]s.mss
function TTIME(text) {
  var value = 0;
    
  str = "" + text;
  if (str.length == 0) return str;
  str = str.split(":")

  switch(str.length) {
    case 3:
      value += Number(str[str.length-3])*60*60;
    case 2:
      value += Number(str[str.length-2])*60;
    case 1:
      value += Number(str[str.length-1]);
  }
  
  return value;
}

function PRETTYTIME(time) {
  var value = Number(time);
  var return_string = "";

  if (value >= 3600) {
    return_string += Math.floor(value/3600) + ":";
    value = value % 3600;
    if (value/60 < 10) return_string += "0";
  } if (value >= 60) {
    return_string += Math.floor(value/60) + ":";
    value = value % 60;
    if (value < 10) return_string += "0";
  } return_string += value.toFixed(3);
  
  return return_string;
}
Solution

  • 01 Display Time
    To get the duration displayed as needed.

    s.mss
ss.mss
m:ss.mss
mm:ss.mss

    You need a separate column D, paste this formula in D2, Take a look at This Sheet.

    =ArrayFormula(IF(B2:B="",,REGEXEXTRACT(TEXT(B2:B,"h:mm:ss.000"),"[1-9].+")))

    enter image description here

    Explanation

    1 - Format the number in this case Date/Time with TEXT function and set format to "h:mm:ss.000".

    2 - REGEXEXTRACT the output with the regular_expression "[1-9].+" meaning check for the first number from the set [1-9] all number except 0 zero and +. everything behind it.

    3 - ArrayFormula(IF(B2:B="",, to calculate only when the cells of the range B2:B is not empty.

    02 Sum and subtraction

    You can add and subtract normally, Google sheets see math on date and time as numbers, just use the appropriate format like this.

    Go to Format > Numbers > Custom date and time.

    enter image description here