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.)
Solution 3
The answer from Hot Licks and spex worked for me in Google sheets - enclosing the h
in []
, as in TEXT(A2,"[h]:mm:ss")
, allows a duration larger than 24 hours. Without the []
, 28 hours shows up as 4, with the [h]
, 28 hours shows as 28.
Christiaan Westerbeek
For about 20 years I have been helping businesses by understanding their needs and use cases and to build them web apps. All the while I was trying to make the latter part of my job one that is more efficient and enjoyable by creating more value in less time while using open-source libraries. In an ever evolving environment where SaaS-platforms are getting better and better, my journey now led me to a point where I believe that for most business applications we don't even need code anymore, most of the time. I'm here to challenge myself and businesses in a quest for the best solutions out there with little time and resources at hand to see if we can evolve the user landscape with apps built with no-code.
Updated on July 11, 2021Comments
-
Christiaan Westerbeek almost 3 years
In the new Google sheets there's a way of formatting a number as a duration. Format -> Number -> Duration.
1
is formatted as24:00:00
1.2
is formatted as28:48:00
1.5
is formatted as36:00:00
0.03125
is formatted as0: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 as24:00
1.2
is formatted as28:48
1.5
is formatted as36:00
(I don't need12:00
which datetime-formatting would accomplish)0.03125
is formatted as0:45