Thanks for taking the time to respond Jerry. Hope you don't mind my
name-dropping, but I know I've learned a few things from your posts dealing
w/XL accuracy issues so it's probably where I would start.

I was a little perplexed the number could not be displayed to more places
than hh:mm:ss.000 (I see I left a 0 off my earlier post) - but was unsure if
there was a reason for it or just MS's decision. Your explanation regarding
that is much appreciated.





"Jerry W. Lewis" wrote:

Thanks for the plug.

Excel time values are stored as a decimal fraction of 24 hours. Thus the time
5:05:25.8127339 PM
would be represented as
=17/24+5/24/60+25.8127339/24/60/60
which equals 0.712104314049769, not the imported value of 0.7121043171296300
which corresponds to the rounded time of 5:05:25.813 PM.

Only 8 decimal places are required to specify a time to a thousandth of a
second, so Excel's 15 digit limit is irrelevant here. You can display the
time to a thousandth of a second with the custom format "h:mm:ss.000 AM/PM".

Why Excel will not import time values to greater accuracy than a thousandth
of a second is a mystery. Excel dates are stored as the number of days since
1900, and date-time values as the date value plus the time value. That only
requires 13 figures until the year 2173, so Excel could easily support an
additional figure or two in time values without losing accuracy in date/time
values. I am not aware of why MS chose not to do so.

Jerry

"JMB" wrote:

Forgot to mention - you might try to search this site for any old posts
w/similar issues and maybe a better solution to your problem.

Specifically, Jerry W. Lewis is one person who is quite knowledgable about
XL accuracy issues.


"Pete Fraser" wrote:


"JMB" wrote in message
...
As you have already noted, the underlying decimal value is:
0.7121043171296300

I count more than 9 digits.

Got it now, thanks.
Excel is rounding the input to nine digits then
doing the conversion.

Is there any way to coerce Excel into doing
arithmetic more accurately, or do I need to
fall back on Python and Mathematica?

Also, it's a minor detail, but do you know how
to get Excel's time format to display to a resolution
better than one second.

Thanks

Pete