Google SpreadSheet - handling MM:SS.sss time formats

Question

I'd like to process the following columns in a google-spreadsheet. The Time column represents the minutes, second and milliseconds take to run 1km and I'd like to be able to sum the four values.

Split   Time
1       3:13:4
2       3:20:5
3       3:16:1
4       3:26:3

I suspect that I need to convert and split the time column into a specific minute and second columns to achieve this goal but would appreciate any advise that the developer may have.

I updated the format of the time column and used the SPLIT / CONTINUE functions

Minutes=SPLIT(B2,":")
Seconds=CONTINUE(C2,1,2)
Total Seconds=(C2*60)+D2

The table now looks like

Split Time    minutes   Seconds Total Seconds
1   03:13:00    3   13  193 
2   03:15:00    3   15  195 
3   03:16:00    3   16  196 

Still wondering about the most efficient way to convert the Total Seconds value to time.

Answer 1

You can use the LEFT(text, number), MID(text, start, number), and RIGHT(text, number).

In detail:

Minutes = LEFT(B2, 1)
Seconds = MID(B2, 3, 2)
Milliseconds = RIGHT(B2, 2)