Google SpreadSheet - Processing MM: SS.sss Time Formats

Question

I would like to process the following columns in a Google spreadsheet. The Time column represents the minutes, seconds, and milliseconds needed to run 1 km, and I would like to summarize the four values.

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

I suspect that in order to achieve this, I need to convert the time column and divide it into columns with minutes and seconds, but I would appreciate any advice the developer might get.

I updated the temporary column format and used the SPLIT / CONTINUE functions

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

Now the table 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 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) 

Answer 2

You can simply use the SUM for these values, a la:

 =SUM(A1:A4) 

Alternatively, you can use functions such as HOUR , MINUTE, and SECOND to retrieve the corresponding values ​​if you want a smaller control.

Answer 3

On the source sheet in a new column:

 =TO_DATE("00:0" & left($B2,4)) 

Then copy the formula down the column. This converts your M: SS (the remaining 4 characters of your data) into the system date / time format for each entry in column B. Then you can summarize and format the results as you like. This assumes that your data does not have leading zeros. You can add a code to check this, but if your times all have one digit for the value of minutes, it will not matter.

Answer 4

If the source data is specified (that is, the "Time" column represents minutes, seconds and milliseconds), then you can add to a reasonable result (795.013 seconds for the first sample of four) a transformation similar to:

  =60*index(split(B2,":"),0,1)+index(split(B2,":"),0,2)+index(split(B2,":"),0,3)/1000 

required.

To convert the sum (presumably in C6) to the same absurd format as for input ( 13:15:13 ):

 =int(C6/60)&":"&int(mod(C6,60))&":"&value(mid(C6,find(".",C6)+1,3)) 

"