milliseconds diff

Question

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.

sergeibaklan · Answer

MosheAvraham40&nbsp;
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
&nbsp;

mosheavraham40 · Answer

SergeiBaklan&nbsp;Thanks for reply.Still getting #VALUE!In what format-cell should A1 &amp; B1 be?Both have data in YYYY-MM-DD HH:MM:SS:SSSS&nbsp; &nbsp;format&nbsp;

sergeibaklan · Answer

MosheAvraham40&nbsp;
Nope, the format is with dot before milliseconds

formula is

mosheavraham40 · Answer

SergeiBaklan&nbsp;Thanks agai.It helps.&nbsp; I need to change :ssss to .sss&nbsp; and reproduce file.Any other format I can use instead of re-create file?Thanks for suggestions.&nbsp;

sergeibaklan · Answer

MosheAvraham40&nbsp;
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.
&nbsp;
One more variant is split datetime on parts with Text to Columns and after combine columns back by formulas.
&nbsp;
But perhaps it'll be easier to correct source data.

Forum Discussion

milliseconds diff

Share

Resources