4

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

enter image description here

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.

0

4 Answers 4

7

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.

0
3

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.

2

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.