Hfrhyu

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