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

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.

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.

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.

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.

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"))))
``````