7

I have a spreadsheet with two columns that count hours and minutes, respectively, with sums to totals at the bottom. I would like to make it so when the minutes reach sixty, it automatically subtracts sixty minutes and adds one hour to the total, that way I am not left with 4 hours and 346 minutes.

It currently looks like this:

Hours  Minutes
1        30
5        35
0        45
2        15
Total  Total
8        125

But I'd like it to look like this:

Hours   Minutes
1        30
5        35
0        45
2        15
Total   Total
10       05

How can I do this?

6

With the following two formulas you can do that.

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

1

Alternative:

in A7: =sum(A2:A5)+int(sum(B2:B5)/60) and
in B7: =mod(sum(B2:B5),60)

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.