How to get difference in ss.000 of 2 YYYY-MM-DD HH:MM:SS.000 dates in Excel-CodePudding

I have an xlsx table with 2 columns, start and end with values YYYY-MM-DD HH:MM:SS.000 (for the sake of clarity let's say I have precision up to milliseconds). Start and end columns don't have any format.

How to calculate the difference in SS.000 between these 2 columns?
Any attempt at formatting start/end columns to make Excel "recognize" the YYYY...SS.000 format didn't work, I don't know why. (I tried truncating start and end to HH:MM:SS.000 in cells, right clicking on relevant cells > format > custom > HH:MM:SS.000 > error message with Excel not allowing the use of that format.)

CodePudding user response：

How to get difference in ss.000?

Assuming:

Text looking like date-time stamps;
A decimal comma as local delimter.

Try:

Formula in C1:

=SUBSTITUTE(B1,".",",")-SUBSTITUTE(A1,".",",")

Important: Cell formatting is set to: ss,000

If you happen to have the decimal point then (untested), simply try:

=B1-A1

With formatting: ss.000.