New Contributor

# milliseconds diff

I have excel Office Professional 2016 file with dates on A1 and B1  format  YYYY-MM-DD HH-MM-SS:SSSS and need to get the diff in milliseconds.    Could not get it via DATE nor TIME functions.

9 Replies

# Re: milliseconds diff

Taking into account that in Excel 1 Hour is equal to number 1/24, in one hour 60 minutes, etc. difference in milliseconds could be calculated as

``=(B1-A1)*24*60*60*1000``

# Re: milliseconds diff

Still getting #VALUE!

In what format-cell should A1 & B1 be?

Both have data in YYYY-MM-DD HH:MM:SS:SSSS   format

# Re: milliseconds diff

Nope, the format is with dot before milliseconds

formula is

# Re: milliseconds diff

@Sergei Baklan Thanks agai.

It helps.  I need to change :ssss to .sss  and reproduce file.

Any other format I can use instead of re-create file?

Thanks for suggestions.

# Re: milliseconds diff

If millisecond are separated by colon, entire datetime is represented as text, As variant you may parse such texts on two parts separating milliseconds, convert both parts to numbers and sum them. Concrete formulas depends on source format, e.g. milliseconds are always have fixed length or not.

One more variant is split datetime on parts with Text to Columns and after combine columns back by formulas.

But perhaps it'll be easier to correct source data.

# Re: milliseconds diff

I have time(hour, minute, second and millisecond) and I need to get the millisecond for the entire column. How to convert all the date parts into only millisecond column.. Thanks

# Re: milliseconds diff

If you have actual time, not text which is looks like time, variant could be

``=MOD(A1,1/24/60/60)*24*60*60*1000``

# Re: milliseconds diff

The time recorded for each second, I need to calculate time difference, my data is sensor data (acceleration x, y, z). I am trying to calculate total distance which needs the time difference like(c2-c1) for all rows.
Distance=(initial velocity*timediff+0.5*acceleration*timediff^2), so the initial velocity=0 but i was not able to get the actual distance, i am using python to calculate the distance

# Re: milliseconds diff

@AishaEdrah
I'm not sure about the formula and in which form and with which precision sensor returns the time.

Back to Excel, in it 1 millisecond is equal to 1/24/60/60/1000 (~1.157e-08).

As an example, you may add two time values in A1 and A2, formula below correctly returns difference in milliseconds.