# How to add minutes and seconds only?

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

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

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

## 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

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