Forum Discussion

Copper Contributor

May 20, 2020

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.

excel

SergeiBaklan

Diamond Contributor

May 21, 2020

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.

AishaEdrah

Copper Contributor

Nov 23, 2022

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

SergeiBaklan
Diamond Contributor
Nov 26, 2022
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
- AishaEdrah
  Copper Contributor
  Nov 26, 2022
  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
  - SergeiBaklan
    Diamond Contributor
    Nov 27, 2022
    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.