How can I add minutes and seconds only in a Google Spreadsheets?
Example: I want to add 1201:01 to 390:35.
Google-sheets – How to add minutes and seconds only
google sheets
Related Solutions
You should be able to do that quite easy with a summation (do use colons (:) in the notation):
A1=00:13:45A2=00:05:12
Then use SUM formula:
SUM(A1:A2)
The result will be: 00:18:57
UPDATE 18-01-2013
See example file I´ve created: Google Spreadsheets: Counting minutes/seconds
If you use Neo's formula, to get rid of the points, in combination with an ARRAYFORMULA you will get the result as well:
=ARRAYFORMULA(SUM(TIME(LEFT(B2:B3,2), MID(B2:B3,4,2), RIGHT(B2:B3,2))))
See example file (+1 for Neo)
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
Minutes
Note
Use a custom format (Format > Number > More Formats > Custom number format...) to obtain a leading zero, see screenshot:
Example
I've prepared an example file for you: How to Turn Leftover Minutes into Hours
Related Topic
- Google-sheets – Get duration as seconds google spreadsheet
- Google-sheets – Adding minutes to base time
- Google-sheets – How to turn a Google Sheets Duration Result from hours:minutes into just minutes
- Google-sheets – How to calculate a monetary value for a given duration in minutes and seconds
- Google-sheets – Convert from duration ( HH-MM-SS) to minutes
- Google-sheets – Google Sheet filling with minutes
Best Answer
Apologies for the long and convoluted formula, but this assumes the values to be summed are in A1:A5 (some of those cells may be blank and will be ignored, but in it's current form the range has to be vertical and no more than one column wide), that these values are text strings in the format that you described, and that you are set to a locale that does not use a comma for a decimal point:
=ArrayFormula(JOIN(":";TEXT(ROUNDDOWN((MOD(SUM(REGEXREPLACE(A1:A5;"([0-9]+):([0-9]+)";{"$1","$2"})*{60,1})/60;{1E293;1})+{0;0.01})*{1;60});{"0";"00"})))edit: based on Jacob Jan Tuinstra's comment that pointed out an inaccuracy, I have had to make it even more convoluted to mitigate against an occasional floating point error; this is the reason for the
+{0;0.01}.