Google Sheets – Human Readable Duration Values

google sheets

I'd like to have a column where I can enter a human readable duration that is not ambiguous (to users who don't know the spreadsheet).

I'd like to use the follow format:

  • 1m
  • 2h 50m
  • 20s
  • 20sec

There is no need for more units (minutes, hours and seconds are fine).

If I select duration under Format -> Numbers, I can only enter durations like this: hh:mm:ss, which is not as readable as 1m or 2h 50m.

Is there a way to add this custom format and apply functions such as SUM on it?

Best Answer

There are two parts to the story: formatting cells and interpreting input.

Formatting

You can impose the format like 1h 23m 45s by going to "Format > More formats > More date and time formats" and selecting the following:

format

To achieve this, use the dropdown in the box to select the appropriate fields, and enter the letters h m s manually. "Elapsed" in the leading field means it's a duration, not a datetime.

The result:

cell

However, durations under 1 hour will be displayed as 0h 23m 0s or 0h 0m 10s; the zeros do not get dropped.

Interpreting input

To have text input such as "2h 30m" or "12m 45s" converted to duration when entered, one needs a script. Here it is: enter it in Script Editor found under Tools, and save. 

function onEdit(e) {
  var value = e.value;
  if (typeof value == 'string') {
    var match = value.match(/(\d+) ?h/i);
    var hours = match ? match[1] : 0;
    match = value.match(/(\d+) ?m/i);
    var minutes = match ? match[1] : 0;
    match = value.match(/(\d+) ?s/i);
    var seconds = match ? match[1] : 0;    
    if (hours || minutes || seconds) {
      var duration = hours/24 + minutes/1440 + seconds/86400;
      e.range.setValue(duration).setNumberFormat('[h]"h "m"m "s"s"');
    }
  }
}

The script parses text input, extracting hours, minutes, and seconds if available. It then calculates the duration and formats the cell in the way described above.

If using this script, you probably will not have to format any cells manually: the functions like =sum tend to inherit format from the cells being summed.

Related Topic