Need to *remove* milliseconds from timestamp

[DEMDEJ]

Hi all! I'm trying to remove milliseconds from timestamps. I have two columns from 2 different tables where Column 1 does not have milliseconds and Column 2 does. I don't want the milliseconds in Column 2 to round any of the data so I need to remove the milliseconds altogether and still be able to perform basic calculations with Col2 (Subtraction and Average).

Here is what I have:

Col1 Col2 Difference (Col2 - Col1)
05:07:34 05:08:39.670 00:01:05.670

What I want:
Col1 Col2 Difference (Col2 - Col1)
05:07:34 05:08:39 00:01:05

What I don't want:
Col1 Col2 Difference (Col2 - Col1)
05:07:34 05:08:40 00:01:06


Just changing the format doesn't work because the number is stored with milliseconds.
I've tried a text formula: = (TEXT(B2,"hh:mm")&LEFT(TEXT(B2,":ss.000"),3)) with a help column VALUE(B2), but my Difference column shows an error.
I've tried INT formula, but that removes my seconds =INT(B2*1440)/(1440)
I've tried using LEFT formulas, but I think it's still text that way. I can't format it either way.
Lastly, I tried =TIME(HOUR(B2),MINUTE(B2),SECOND(B2)), but the seconds are rounded by the milliseconds.

I still need to be able to subtract and average Column 2. Any formulas are welcomed. I prefer not to use macros for such a seemingly small task. I refuse to say how long I spent on this, but trust me, ANY help would be greatly appreciated! Help !

 

[Habtest]

Try this

=FLOOR(B1,1/24/60/60)

 

[DEMDEJ]

Try this

=FLOOR(B1,1/24/60/60)

You are amazing, my friend!

Thank you!

 

[Habtest]

No problem, glad it works

 

[FormR]

Hi, you can also write the function like this, which might help make the intention clearer.

 =FLOOR(B1,"00:00:01")