0

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

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
PizzaKebab
  • 11
  • 5
  • 1
    Excel does not support millisecond precision in dates. If you are happy to lose them, see https://stackoverflow.com/q/47951860/11683. – GSerg May 03 '22 at 11:09
  • 1
    Since the desired diff is with a decimal comma, can we assume your locale settings are the same and therefor the data showing under 'start' and 'end' are string-data? – JvdV May 03 '22 at 11:34
  • 2
    I have tried this and it worked for me and the output was **`1,148`**, however i dont think excel can hold that values as a number may be its in text format, therefore if its in text format, then you may give a try, but the formula is bit ugly, `=((LEFT(B2,FIND(" ",B2)-1)*1-LEFT(A2,FIND(" ",A2)-1)*1)+(MID(B2,12,8)*60*60*24-MID(A2,12,8)*60*60*24)+(RIGHT(B2,10)-RIGHT(A2,10)))` – Mayukh Bhattacharya May 03 '22 at 11:52
  • 2
    @PizzaKebab and you are not able to change the format since am sure those are formatted as text, and from the screenshot it shows its left aligned, in excel text are always left aligned while dates and times which are stored as numbers in excel are by default right aligned, but if you press `CTRL ~` it will show you left aligned which confirms those are actually texts and not dates – Mayukh Bhattacharya May 03 '22 at 12:03
  • 1
    It worked with the big formula ; JvdV's solution is neater though. Big thanks anyway :) – PizzaKebab May 03 '22 at 13:03

1 Answers1

2

How to get difference in ss.000?

Assuming:

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

Try:

enter image description here

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    @MayukhBhattacharya, maybe I'm overlooking something but since these are two text-strings in a valid date-time format we can just subtract them. Excel will recognize the format even though it's text (it worked fine for me). Formatting the result in `ss,000` showed the precise answer OP is looking for. To multiply that by 24*60*60 would create a number much larger (though looking the same if one would format that as 'number'). – JvdV May 03 '22 at 12:24
  • Sir, but when i simply used B1-A1 it gave me some scientific notation – Mayukh Bhattacharya May 03 '22 at 12:25
  • 1
    Exactly @MayukhBhattacharya, hence the cell formatting `ss,000` or `ss.000` depending on your locale. – JvdV May 03 '22 at 12:26