68

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
Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
Christiaan Westerbeek
  • 10,619
  • 13
  • 64
  • 89

6 Answers6

71

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

KyleMit
  • 30,350
  • 66
  • 462
  • 664
AJPerez
  • 3,435
  • 10
  • 61
  • 91
  • 1
    Thanks for the extensive elaboration, but I specifically asked for duration. That is to say that the value of 1.5 should be formatted as 36:00 and not 12:00. 12:00 is what the datetime-formatting is going to accomplish. – Christiaan Westerbeek Sep 07 '14 at 18:14
  • 4
    Actually I like this answer better now. I just was mislead by the spanish language in the screenshot. And I didn't notice the duration word somewhere in the middle of your text. – Christiaan Westerbeek Sep 07 '14 at 18:44
  • ¿If I have my cells in minutes which is the formula to convert minutes to use this format? – xav56883728 Feb 10 '19 at 21:49
  • 1
    @xav I think you just need to divide the minutes by 60*24, to convert them into days – AJPerez Feb 11 '19 at 08:03
  • I wanted to do mm:ss and had a problem with step 2: trying to type the colon wasn't working. What I did is added Elapsed Seconds and then deleted that, which gave me the cursor to enter the colon, then added Elapsed Seconds after. – Ddddan Apr 28 '19 at 13:39
  • This works, but currently I experience the following bug: I could not find the tiny arrow that is shown in the textbox (to open the different available options). I figured out that it is now almost completely hidden by the "Apply" button but one can still click on. – T-Dawg Feb 10 '22 at 12:00
15

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.

RobertMyles
  • 2,673
  • 3
  • 30
  • 45
YYR
  • 181
  • 1
  • 2
  • 1
    Working with the GSheets API, I was setting `[hh]:[mm]:[ss]` as format, and `TIME` as type for a duration, and it formatted it weird, as if it tried half-way to format it into a date. As mentioned by Yaakov, setting it only as `[h]:mm:ss`, correctly outputs a duration (the difference between two dates, in hh:mm:ss). Super weird behavior which was bugging me for hours. – Nico Villanueva Feb 07 '19 at 11:06
14

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.)

Hot Licks
  • 47,103
  • 17
  • 93
  • 151
  • Sorry, but that's really all I know. I'm no spreadsheet whiz -- I just try to get along, and, as I said, I found this trick somewhere about a year ago. In Excel it's highlight the cell, RMB, Format Cells, then select the Number tab, Custom, and type in the pattern. And "Help" in my Excel is broken right now, so I can't really search for more. – Hot Licks Sep 07 '14 at 18:40
  • 2
    This answer helped me. The accepted answer works for formatting a whole cell, but I wanted to format only a part of the contents of a cell. I had tried using `TEXT(A2,"hh:mm:ss")` but this is a date format and so does not work for negative durations. However, `TEXT(A2,"[hh]:mm:ss")` does work for negative durations. Thank you @HotLicks :) – spex Nov 18 '15 at 18:35
  • Right now, this doesn't work for me. 25 hours displays as 1:00 – Jonathan Hartley Oct 18 '18 at 14:16
1
  1. Select Format > Number > More Formats > Custom number format.
  2. Enter [h]:mm and click Apply.
yoyo
  • 8,310
  • 4
  • 56
  • 50
0

Basically Menu choices are:

  • Format > Number > Custom Date and time

Then, choose duration properties putting commas between them

These are Korean, however, the idea may well understood.

Park JongBum
  • 1,245
  • 1
  • 16
  • 27
0

TRY THIS
To display 1.00 [Hrs] when you enter 1 ...
Navigate to:
Format > Number > Custom number format

In the formula bar use the following:    #,##0.00 "[Hrs]"
"[Hrs]" can be replaced with anything, just make sure you keep the "double quotes"

You should get the following displayed

1    =>   1.00 [Hrs]
1.5   =>   1.50 [Hrs]
15.2 => 15.20 [Hrs]

Dwerg01
  • 1
  • 1
  • 2