18

How to add minutes in Google Spreadsheet?

I've got a Google Spreadsheet that looks roughly like this:

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?

1
14

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

3

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

1
  • 1
    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
2

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 )

Not the answer you're looking for? Browse other questions tagged or ask your own question.