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.
Best Answer
With a time in cell A1, use:
yields milliseconds as text, and if you need it as a number:
(the second formula gives the number of milliseconds as an integer)
Similar formulas can capture hours or minutes or seconds as integers.