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](https://i.stack.imgur.com/ZmgW0.png)
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](https://i.stack.imgur.com/AKFxQ.png)
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.
Best Answer
Suppose your posted raw data were in A2:A. In B2 of an otherwise empty range B2:B, place the following formula:
=ArrayFormula(IF(A2:A="",,A2:A/24/60))
Then select Col B entirely and apply the following format:
Format > Number > Custom number format > hh:mm:ss
/24/60
divides your raw numbers by 24 hours per day and then 60 minutes per hour. You could just useA2:A/1440
, but I find that seeing/24/60
makes more sense to most people.