Google Sheets – How to Show Hours Over 24

google sheets

If one sums a number of cells that contains time data, how does one sum it and format it to show hours and minutes and not days? For example if the times 20:20 & 08:10 are summed, the result formatted by hours and minutes is 04:30, what is required is 28:30

How can a cell be formatted to display total hours?

Best Answer

Let's say you have these numbers in A1, A2

Apply TIMEVALUE() to B1, B2
SUM all these values then multiply by 24 into B3
Apply this in any other cell: =ROUNDDOWN(B3)&":"&60*(B3-(ROUNDDOWN(B3)))

Formula

Hours
=SUM(A2:A5) + HOUR(TIME(0,SUM(B2:B5),0))

alternate way of notation
=HOUR(TIME(SUM(A2:A5),SUM(B2:B5),0))

Minutes
=SUM(B2:B5) - (HOUR(TIME(0,SUM(B2:B5),0)) * 60)

alternate way of notation
=MINUTE(TIME(SUM(A2:A5),SUM(B2:B5),0))

Explained

The hour calculation sums up the hour values and adds the sum of the minutes converted to hours. For the minutes, the calculation is a bit different. Here the sum of the minutes is subtracted by the hour count of those minutes, leaving only minutes ranging from 0 to 59.

In the second tab, the alternate formula is added.

Screenshot

Hours
enter image description here

Minutes
enter image description here

Note

Use a custom format (Format > Number > More Formats > Custom number format...) to obtain a leading zero, see screenshot:
enter image description here

Example

I've prepared an example file for you: How to Turn Leftover Minutes into Hours

Google-sheets – Get duration as seconds google spreadsheet

If your 0:01:00 is a date/time index (numeric rather than a string) the underlying number is an integer for days and a decimal for time. as there are 24*60*60 seconds in a day the number of seconds represented by 0:01:00 is effectively the number of seconds between midnight and one minute past midnight, which for A1 can be calculated as:

=86400*A1