Extracting milliseconds from a time field

[Angus]

I have column A with timestamps in format hh:mm:ss.000 - eg
14:32:05.679 and I want to display the milliseconds component of the
timestamp, eg in this case 679 in cell B. How can I achieve this?

 

[Pete_UK]

Put this in B1:

=MOD(A1*24*60*60,1)*1000

and copy down as required.

Hope this helps.

Pete

 

[Ron Rosenfeld]

I have column A with timestamps in format hh:mm:ss.000 - eg
14:32:05.679 and I want to display the milliseconds component of the
timestamp, eg in this case 679 in cell B. How can I achieve this?

=MOD(A1*86400,1)*1000

--> 679
--ron

 

[joeu2004]

=MOD(A1*86400,1)*1000
--> 679

When I enter 14:32:05.679 in a cell, the MOD function results in
678.999999996449 when formatted as Number with 15 significant digits,
which Excel treats as different from 679. At a minimum, I would do:

=round(mod(A1*86400,1),0)

However, Angus does not explain the origin of the timestamps. If it
is a function similar to NOW(), the timestamp includes a "serial
number" (days since 12/31/1899 on non-Macs). In that case,
multiplying by 86400 is likely to exceed the MOD limit of
134217727+0.9999995. That could be avoided by:

=round(mod(mod(A1,1)*86400,1),0)

Alternatively, the following remedies all of thes problems:

=--right(text(A1,"ss.000"),3)

PS: It appears that with Excel 2003 on MS Win XP, the resolution of
NOW() is indeed milliseconds, not less. The fractional digits in the
result from MOD(MOD(A1,1)*86400,1) is due to "numeric
error" (aberration) when NOW() is computed by serialNumber + hr/24 +
min/1440 + sec/86400 + msec/86400000. At least, that is how I
duplicated exactly the internal representation of NOW(). (But
sometimes, it requires VBA, which seems to provide 80-bit FP precision
over the entire expression).

 

[Ron Rosenfeld]

When I enter 14:32:05.679 in a cell, the MOD function results in
678.999999996449 when formatted as Number with 15 significant digits,
which Excel treats as different from 679. At a minimum, I would do:

=round(mod(A1*86400,1),0)

However, Angus does not explain the origin of the timestamps. If it
is a function similar to NOW(), the timestamp includes a "serial
number" (days since 12/31/1899 on non-Macs). In that case,
multiplying by 86400 is likely to exceed the MOD limit of
134217727+0.9999995. That could be avoided by:

=round(mod(mod(A1,1)*86400,1),0)

Alternatively, the following remedies all of thes problems:

=--right(text(A1,"ss.000"),3)

PS: It appears that with Excel 2003 on MS Win XP, the resolution of
NOW() is indeed milliseconds, not less. The fractional digits in the
result from MOD(MOD(A1,1)*86400,1) is due to "numeric
error" (aberration) when NOW() is computed by serialNumber + hr/24 +
min/1440 + sec/86400 + msec/86400000. At least, that is how I
duplicated exactly the internal representation of NOW(). (But
sometimes, it requires VBA, which seems to provide 80-bit FP precision
over the entire expression).

Yes, if his time stamps are the result of the NOW() function, then MOD can not
be used. But one could use

=(A1*86400-INT(A1*86400))*1000

which does not suffer those same limitations

Your other suggestions are, indeed, useful methods of handling the imprecision
of binary based, limited precision arithmetic when applied to decimal values.

Empirically, I am not able to return a time, using the NOW() function, that has
better precision than 10ms.
--ron

 

[Dave Peterson]

Another way that just uses what's displayed in the cell:
As text:
=RIGHT(TEXT(A1,"hh:mm:ss.000"),3)
or
As a number:
=--RIGHT(TEXT(A1,"hh:mm:ss.000"),3)

I kept the same formatting string to match your original, but you could use:
=RIGHT(TEXT(A1,"s.000"),3)
or
=--RIGHT(TEXT(A1,"s.000"),3)
If you wanted.

 

[joeu2004]

PS:  It appears that with Excel 2003 on MS Win XP,
the resolution of NOW() is indeed milliseconds, not less.

[....]
Empirically, I am not able to return a time, using the
NOW() function, that has better precision than 10ms.

That's not uncommmon. I can confirm your empirical observation; but I
do not know that for a fact.

When I said that the resolution is "milliseconds" (plural ;->), I was
referring to the unit of measure, not the granularity. Arguably,
"resolution" was a poor choice of words. The kernel32 getTickCount
function result is descibed as: "The return value is the number of
milliseconds that have elapsed since the system was started. The
resolution is limited to the resolution of the system timer."

I was anticipating a concern that someone might raise that by rounding
the MOD*1000 result, we are losing a number of microseconds (and
perhaps even nanoseconds!!), the fractional part of the milliseconds.
I demonstrated that is not the case, at least not for MS Win XP.

In any case, Angus specified that he wanted the integral number of
milliseconds (679).

 

[Ron Rosenfeld]

PS:  It appears that with Excel 2003 on MS Win XP,
the resolution of NOW() is indeed milliseconds, not less.

[....]
Empirically, I am not able to return a time, using the
NOW() function, that has better precision than 10ms.

That's not uncommmon. I can confirm your empirical observation; but I
do not know that for a fact.

When I said that the resolution is "milliseconds" (plural ;->), I was
referring to the unit of measure, not the granularity. Arguably,
"resolution" was a poor choice of words. The kernel32 getTickCount
function result is descibed as: "The return value is the number of
milliseconds that have elapsed since the system was started. The
resolution is limited to the resolution of the system timer."

I was anticipating a concern that someone might raise that by rounding
the MOD*1000 result, we are losing a number of microseconds (and
perhaps even nanoseconds!!), the fractional part of the milliseconds.
I demonstrated that is not the case, at least not for MS Win XP.

In any case, Angus specified that he wanted the integral number of
milliseconds (679).

He did.

There's an interesting discussion of obtaining accurate times under Windoze
here: http://www.lochan.org/2005/keith-cl/useful/win32time.html

For what it's worth, QueryPerformanceCounter returns a value of 340016 on my
system, which would suggest an ultimate resolution of about 3 microseconds.
--ron

 

[Stopwatch]

Does anybody know how to tame Excel to handle milliseconds? Even assigning
mm:ss,000 format Excel cuts milliseconds once the cell is being touched to
edit.


http://www.xnotestopwatch.com/

 

[JoeU2004]

Does anybody know how to tame Excel to handle milliseconds?
Even assigning mm:ss,000 format Excel cuts milliseconds once
the cell is being touched to edit.
http://www.xnotestopwatch.com/

Not sure I fully understand what you are doing and what you want, perhaps
because I am unwilling to follow the link in you message for security
reasons.

If you use the Custom format mm:ss.000 (US localization; perhaps mm:ss,000
is correct for other localizations), you should get time with millisecond
resolution. It is true that you will not see milliseconds when you edit the
cell; and if you simply type F2 and Enter, it is true that the time is
rounded to seconds. But the format is not lost. So I work around that
anomaly (defect?) simply by typing the whole thing plus edits.

But your subject line asks about "extracting milliseconds" from the time.
That depends on what form you want. Ostensibly:

=MOD(A1*86400,1)

returns milliseconds as a fraction of a second. But:

=ROUND(MOD(A1*86400,1)*1000,0)/1000

formatted as Number with 3 dp is more accurate, at least in some cases
because of the way that Excel handles decimal fractions. Of course:

=ROUND(MOD(A1*86400,1)*1000,0)

returns milliseconds as a whole number.