Time subtraction with ms involved

Question

I have a csv file with data like this:

1,Success,19:1:6:445

1,Success,19:1:7:652

1,Success,19:1:10:172

1,Success,19:1:11:500

1,Success,19:1:12:893
...

The goal is to calculate the time difference from the row above. For example, the duration from row 1 to row 2 = 1000ms + 652-445ms = 1207 ms

Answer 1

First, load your CSV to excel and parse by using text to columns like below:

Result will be like this:

Use this formula in G1 to get the millisecond difference.
=SUMPRODUCT(C2:F2-C1:F1,{3600000,60000,1000,1})
And there you have your millisecond difference.

Answer 2

Excel doesn't know about milliseconds. When you load your CSV, those "19:1:6:445" entries will come in as text. You'll have to add some formulas to parse them semi-manually. The mutant-date entries will end up in column C, so I'll put formulas starting in column D and going to the right.

In D1, find the position of that third colon, the one that delimits off the milliseconds.

=FIND(":", C1, FIND(":", C1, FIND(":", C1, 1)+1)+1)

In E1, get the date value that Excel can parse, the part with seconds and larger.

=TIMEVALUE(LEFT(C1,D1-1))

and in F1, the date value of the milliseconds portion. That division? Seconds per hour, hours per day, milliseconds per second.

=NUMBERVALUE(MID(C1,D1+1,30))/3600/24/1000

Now, recombine the two in G1,

=E1+F1

and fill those columns down to get the values-with-milliseconds for all the imported cells. The differences in milliseconds can start in row 2, so in H2 you put

=(G2-G1)*3600*24*1000

which converts back to readable milliseconds.

If you know anything about formulas, it shouldn't be hard to combine columns E, F, and G, but I left them separate for clarity's sake. Also note that you're dealing with floating point values here; this means that you will get an occasional off-by-one error.