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?

`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");
}
``````

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