3

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

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

1

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

| 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
  • 1
    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
0

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

| improve this answer | |
0

This might be a rare occasion when Duration format may make sense. Choose the default offer of Duration (Format > Number > Duration) then Format > Number > More Formats > More date and time formats... then remove the second :, Delete Second (1) and Apply.

(The system will 'think' you are adding Elapsed hours and minutes but the arithmetic is the same for minutes and seconds.)

| improve this answer | |

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.