calculate time difference in milliseconds excel

Question

How do i calculate time difference in milliseconds between two columns where value of time has milliseconds component ... i.e. 16:33:44:056. Please refer to column E and J in pic .. i want to calculate difference in milli seconds between J and E ...

I have tried using the following formula to alculate difference in milli seconds but its giving incorrect results. =((RIGHT(J1,3))-(RIGHT(E1,3)))

Answer 1

Try

=(J1-E1)*1000*60*60*24

or more concisely

=(J1-E1)*86400000

Then format your formula column to general.

This assumes the data is actually stored as datetime and not a text value.

Answer 2

If you change the format of your time columns, and the column to show the difference, to

hh:mm:ss.000

You can simply use subtraction:

_{(You'll have to tweak the actual cells to have . before the milliseconds, just formatting won't do it)}

Answer 3

It appears your date/time is a text value.

A "real" time value would normally be seen as 16:11:52.052

But by replacing the last : with a ., excel will see it as a real time and ordinary math can be done.

Excel stores date/time as days and fractions of a day.

So a formula that should work:

=ROUND((SUBSTITUTE(J1,":",".",3)-SUBSTITUTE(E1,":",".",3))*86400000,0)

Format the result as General or as Number with no decimal places

Answer 4

If the part of that string corresponding to hours, minutes and seconds is always the same, and time in J is always bigger than time in E, you could do:

=VALUE(RIGHT(J1;3))-VALUE(RIGHT(E1;3))