Google Sheets – How to Sum MM:SS.sss (Minutes and Seconds)

google sheets

I was looking at a previous post and they used the cell input as follows

=ARRAYFORMULA(SUM(TIME(LEFT(B2:B3,2), MID(B2:B3,4,2), RIGHT(B2:B3,2))))

And it works to a point. But the numbers don't roll over correctly.

For instance if I have the following (note: the numbers are in the weird format to make the equation work).

10.55.99
10.20.12
21:16:51  From the equation
21:16.11  What is should actually be

Is there any way to have this work correctly? Or maybe some other way where I could pull apart the original input and process it outside of being a time?

Best Answer

I miss read your question at first, that's why I gave this answer:

10.55.99 is being converted into 10:56:39, adding 10.20.12 will total 21:16:51

Because in the normal TIME() function, milliseconds aren't accounted for.

What I didn't notice, was the exact notation you're interested in: mm:ss.SSS. This requires a bit of a different approach.

First, you need to calculate the text values to milliseconds, like below:

Formula

SUM(
  ARRAYFORMULA(
    LEFT(A2:A3,2)*60*1000+            // minutes 
    MID(A2:A3,4,2)*1000+              // seconds
    RIGHT(A2:A3,3)                    // milliseconds
  )
)

Secondly, you need to format the time to mm:ss.SSS. There you need to use a little bit of Google Apps Script, to do the formatting like this:

Code

function setFormat(value) {
  return Utilities.formatDate(new Date(value), "GMT+1", "mm:ss.SSS");
}

Screenshot

enter image description here

Note

This solution is tailor-made towards the mm:ss.SSS notation. The advantage is that every entry is seen as a text entry (because the format isn't recognized). A slight disadvantage is the usage of the script (add script under Tools>Script editor, press bug button, authenticate script).

There is yet another way to perform this calculation, but it starts by using this notation: mm:ss:SSS. Advantage is that no script is used. Disadvantage would be that all entries are recognized as time and converted accordingly. Therefore you need to either set the format in advance or start the entry with a single quote. See reference for complete solution by Ahab on the Google Forum

Reference

https://productforums.google.com/d/msg/docs/o9xvG4gYeIc/fBi6ZgrEbfYJ

Related Topic