How do I extract a value from a time formatted value in excel?

Question

How do I extract a value from a time formatted value in excel?

On the values below

column1 - is in this format => hh:mm:ss.000
column2 - is the decimal value of the
column3 - is = to column2 but I have formatted that hh:mm:ss.000

I want to be able to extract the 5 or 005 from the column 3 above. How do I do this? To get the hour, minute, or second, I can use the hour, minute, or second functions respectively.
But how do I extract the thousand second e.g. .005 in this case?

NOTE:
1 second = 1.15741E-05(=1/86400)
0.005*(1/86400) = 0.00000005787 = the decimal presentation of 5 thousand of a second(0.005). All I need to do is change the format of the cell to hh:mm:ss.000 to present it differently.

Answer 1

With a time in cell A1, use:

=RIGHT(TEXT(A1,"hh:mm:ss.000"),3)

yields milliseconds as text, and if you need it as a number:

=RIGHT(TEXT(A1,"hh:mm:ss.000"),3)*1

(the second formula gives the number of milliseconds as an integer)

Similar formulas can capture hours or minutes or seconds as integers.

Answer 2

This will return the fraction of the seconds as an integer:

=MOD(A1*86400,1)*1000

The Mod removes everything but the fraction of the second and the * 1000 causes it to be an integer.

Answer 3

Looks like there are lots of neat ways to do this. Here is another:

=(B8-TIME(HOUR(B8),MINUTE(B8),SECOND(B8)))*86400*1000

Where time is in cell B8.

Answer 4

If I understand correctly, you want the result to be 0.005 as a number

Your best bet may be to extract the fractions of a second as a string and convert that to a number. This should do it:

=VALUE("0." & RIGHT(TEXT(E5,"hh:mm:ss.000"),LEN(TEXT(E5,"hh:mm:ss.000"))-FIND(".",TEXT(E5,"hh:mm:ss.000"))))