milliseconds diff

[MosheAvraham40]

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.

[#2]

@MosheAvraham40 

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

[#3]

@Sergei Baklan Thanks for reply.

Still getting #VALUE!

In what format-cell should A1 & B1 be?

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

[#4]

@MosheAvraham40 

Nope, the format is with dot before milliseconds

formula is

[#5]

@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.

[#6]

@MosheAvraham40 

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.

[#7]

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

[#8]

@AishaEdrah 

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

[#9]

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

[#10]

@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.