Google Sheets – How to Convert 1.33 Hours to 1:20 (Hours:Minutes)

google sheets

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?

Best Answer

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

Related Solutions

Google-sheets – Google Docs Time report – How to add hours

It may be a little late, but here is how I do it:

=HOUR(B2-A2)+(MINUTE(B2-A2)/60)

Basically, being B2 the end time and A2 the start time, you subtract B2-A2 and get the hour from the subtraction. Then you do the same and get the amount of minutes. After you have both, you sum the amount of hours with the amount of minutes divided by 60 (to get it in hours).

Don't forget to use the 24-hour format (i.e. 10:00 or 22:00).

Google Sheets – How to Calculate Time Delta

Google has added a new number format called Duration. Start by formatting the start and end fields to Format -> Number -> DateTime or Format -> Number -> Time and your calculation field to Format -> Number -> Duration

Once you have done that you can subtract the fields to get the difference as noted by Stefano Palazzo in his answer.

Best Answer

Related Solutions

Google-sheets – Google Docs Time report – How to add hours

Google Sheets – How to Calculate Time Delta

Related Topic