9

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?

6

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

1

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 )

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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