# How to convert 1.33 hours to 1:20 (hours:minutes)?

``````min | min | min | min | min | total min | total time
20 |  20 |  20 |  20 |  20 |    80     |    1.33
``````

But I want Total Time to show `01:20` or `01h20min` or something like that.

How can I accomplish that?

I just found this answer for Excel over at http://support.microsoft.com/kb/75618 and it worked for me in Google sheets as well. Returns a serial number that displays correctly as HH:MM when your target cell is formatted as Hours.

``````="Minutes"/1440
``````

(There are 1440 minutes in 24 hrs)

example:

``````=SUM(A1:A5)/1440
``````

results in

``````0.055555555555556
``````

and in turn converts to

``````01:20:00
``````

(when cell formatted as "Hours")

I had problems with with your answers, my fault for sure, or I have problems with the cell formats, that I misunderstood.

BUT, I wrote this one-shot formula that converts a cell with number to a text area with the desired `hh:mm` format.

You can change it adding other strings to the concatenate.

``````=concatenate(TEXT(Floor(H2),"00"),":",TEXT(round((H2-Floor(H2))*60),"00"))
``````

`H2` is the cell containing the decimal number representing the hour of the day.

`4.81` will be translated into `04:49`

• This was spot on - handles values higher than 24 hours as well. I adjusted it to this `=concatenate(TEXT(Floor(H4),"0"),"h ",TEXT(round((H4-Floor(H4))*60),"0"), "m")` to get `1h 9m` or `351h 23m` as an example. – Force Hero Apr 20 '20 at 9:27

I've checked and fixed @RobGazy answer numbers (your number was valid for 4 rows i.e. SUM(A1:A4) but in question were 5 columns so must be SUM(A1:E1)).

Also could't find default "HOUR" format (in the menu) - this is function from the DATE group for Google Spreadsheet. If You apply `HOUR` format from `menu of advanced formats` You will get `01`, not `01:40` So... answer for Your example should be:

``````=SUM(A2:E2)/1440
``````

gives results in F2

``````0,06944444444
``````

and in turn converts to

``````01:40:00
``````

(NOTE: When cell formatted as "Hours" - so You have to choose to apply from quick menu 3 format - number:

• -DATE- wrong
• TIME gives `01:40:00`
• DURATION gives `1:40:00`

and if You want `01:40` - choose from custom: `HOUR:MINUTE` )