Search code examples
exceltimeexcel-formulamilliseconds

calculate time difference in milliseconds excel

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 ... enter image description here

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

Solution

  • 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