How to format a duration as HH:mm in the new Google sheets

Christiaan Westerbeek picture Christiaan Westerbeek · Sep 7, 2014 · Viewed 64.2k times · Source

In the new Google sheets there's a way of formatting a number as a duration. Format -> Number -> Duration.

  • 1 is formatted as 24:00:00
  • 1.2 is formatted as 28:48:00
  • 1.5 is formatted as 36:00:00
  • 0.03125 is formatted as 0:45:00.

I don't need the seconds in the duration representation, because they bloat my timesheet.

How can I format a duration as HH:mm (without using a formula to calculate hours and minutes and concatenate that with a colon). Is there a way of using the TEXT formula.

This is how I would like it:

  • 1 is formatted as 24:00
  • 1.2 is formatted as 28:48
  • 1.5 is formatted as 36:00 (I don't need 12:00 which datetime-formatting would accomplish)
  • 0.03125 is formatted as 0:45

Answer

AJPerez picture AJPerez · Sep 7, 2014

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:

  1. 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.
  2. Type your separator text :
  3. Click on the arrow again and add the second element: Elapsed Minutes.
  4. Finally, click on each element you added, to determine the correct format (trailing zeros or not, for example).

Custom Date Format Screenshot