Google Sheets – How to Convert Minute Number to Human-Readable Format

This option is only available in the "new Google Sheets".

In the new Google Sheets, you can apply custom formatting for currencies, dates, and numbers.

cf https://support.google.com/drive/answer/56470?hl=en

To activate the new Google Sheets, you can follow those instructions: https://support.google.com/drive/answer/3541068

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:

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:

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.