How to format a duration as HH:mm?

Question

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

Annemarie

Can't you just set the cell's Date/Time format? – Infix

No because 1,5 becomes 12:00 using date/time formatting and I need 36:00 – Annemarie

In Excel it's "Custom" then [hh]:mm. – Infix

Answer 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:

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

Neuroma

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. – Annemarie

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. – Annemarie

¿If I have my cells in minutes which is the formula to convert minutes to use this format? – Kersey

@xav I think you just need to divide the minutes by 60*24, to convert them into days – Neuroma

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. – Quean

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. – Frizzy

Answer 2

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.

Astereognosis

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. – Ramsgate

Answer 3

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

Infix

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. – Infix

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 :) – Saloma

Right now, this doesn't work for me. 25 hours displays as 1:00 – Ethridge

Answer 4

1. Select Format > Number > More Formats > Custom number format.
2. Enter [h]:mm and click Apply.

Armidaarmiger

Answer 5

Basically Menu choices are:

• Format > Number > Custom Date and time

Then, choose duration properties putting commas between them

Sandon

Answer 6

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]

Ignite