How to format a duration as HH:mm in the new Google sheets
Solution 1:
There is no need to use formulas for that, you can define your own custom formats.
Just go to Format -> Number -> More formats -> More date and time formats. It will open a window with several date and time formats to choose from. You can define your own as well, using the upper text area:
- Click on the small arrow on the left, and select the first element you want to add: elapsed hours. Notice there are two different "hours", one for time and another for durations.
- Type your separator text
:
- Click on the arrow again and add the second element: Elapsed Minutes.
- Finally, click on each element you added, to determine the correct format (trailing zeros or not, for example).
Solution 2:
In Excel it's "Custom" then [hh]:mm
.
(I'm not quite sure how I figured this out, but it's in a spreadsheet I've been using for a year or so.)