1

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?

4

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.

  • Thanks for the answer, but manually entered values are not recognized. i.stack.imgur.com/zqnA7.png The sum function should calculate the total time spent. I have applied the custom format to either row. – Matt3o12 Aug 1 '16 at 21:44
  • I see, you want to enter durations in the form like 1h 20m 10s... – user79865 Aug 1 '16 at 23:01
  • I edited the answer accordingly. – user79865 Aug 2 '16 at 0:24
  • Works like a charm! Thank you very much :) – Matt3o12 Aug 3 '16 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.