Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

How can I add minutes and seconds only in a Google Spreadsheet?

Example: I want to add 1201:01 to 390:35.

share|improve this question
add comment

2 Answers 2

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}.

share|improve this answer
    
If you add 1201:01 to 390:35 it must yield 1591:36. Your formula gives 1591:35. I've added the formula in the example I've prepared, so you can have a look. –  Jacob Jan Tuinstra Jun 25 '13 at 6:36
    
Another difference is when you do this: '1201:25 + 390:35. Your formula writes 1592:00 as where mine writes 1591:60. –  Jacob Jan Tuinstra Jun 25 '13 at 7:04
1  
Thanks Jacob; regarding the first example, well spotted, another example of Sheets not masking floating point arithmetic errors. I have edited my answer accordingly, but you might want to test it out. –  AdamL Jun 25 '13 at 21:49
    
Regarding the second example, wouldn't 1592:00 be correct? Also note something like 1201:59 + 390:59. –  AdamL Jun 25 '13 at 21:51
add comment

Starters

When you try to add 1201:01 into your spreadsheet, it will be automatically converted into a correct time format: 1201:01 becomes 20:01:01.
Therefore you need to add '1201:00, so that the spreadsheet thinks it's a text entry.

Calculation

First retrieve the minutes:

=INDEX(SPLIT(A2,":"),1,1)+INDEX(SPLIT(B2,":"),1,1) == (MINUTES)

and then the seconds:

=INDEX(SPLIT(A2,":"),1,2)+INDEX(SPLIT(B2,":"),1,2) == (SECONDS)

and combine the two:

= MINUTES & ":" & SECONDS

Roundup

If you want to convert the total into a hour based value, then add this formula:

=TIME(0,INDEX(SPLIT(A2,":"),1,1),INDEX(SPLIT(A2,":"),1,2))
  +TIME(0,INDEX(SPLIT(B2,":"),1,1), INDEX(SPLIT(B2,":"),1,2))

Note

Don't convert the column to the correct time format, as 26:31:36 will be changed to 02:31:36.
See example file I prepared: Minutes and Seconds only

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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