Excel time format / accuracy

[Pete Fraser]

A two part question.
I'm reading in a csv file to Excel 2007.
It has a series of time values, for example:

"5:05:25.8127339 PM"

I can't work out how to format the cell as a
time, and keep the decimal (the display rounds to the
nearest second).

I decided just to ignore that, and just formatted as a number.
It seems to display correctly (as the correct portion of a
day), but only to an accuracy of the first three input digits
after the decimal point.

For example, the time above would convert to
0.7121043171296300 which is equivalent to
"5:05:25:.8130000 PM".

What happened to my accuracy, and how do I
get it back?

Thanks

Pete

 

[Guest]

I think your problem is that XL can only accomodate numbers to 15 digits.
Anything after that is truncated.

 

[Pete Fraser]

I think your problem is that XL can only accomodate numbers to 15 digits.
Anything after that is truncated.

I'm not getting that though.
It's only giving me 9 digits
(2 hours, 2 minutes, 2 seconds, 3 miilliseconds).

 

[Guest]

As you have already noted, the underlying decimal value is:
0.7121043171296300

I count more than 9 digits.

 

[Pete Fraser]

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

 

[David Biddulph]

Format as hh:mm:ss.000

 

[JMB]

XL chops off the number after 15 digits and puts in zeros (placeholders).
You can get XL to display all of the digits by formatting the cells as text,
but that won't help accuracy if you perform arithmetic operations.

You may need to use something other than XL. You might also look around for
any XL add-ins that might help overcome this limitation (I think I've seen
one before, but have no clue where to find it now).


You could look at using VBA since it has data types that can accomodate more
digits. Your data would need to be stored in the cell as text. Suppose A1
is '1.2345678901234567890, then Test(A1, "*", 2) returns 2.469135780246913578

I don't know of a way to get XL to display times to a greater degree of
accuracy than hh:mm:ss.00

Function Test(strInput1 As String, _
strOperation As String, strInput2 As String) As String
Dim varInput1 As Variant
Dim varInput2 As Variant

varInput1 = CDec(strInput1)
varInput2 = CDec(strInput2)

Select Case strOperation
Case "*"
Test = CStr(varInput1 * varInput2)
Case "/"
Test = CStr(varInput1 / varInput2)
Case "+"
Test = CStr(varInput1 + varInput2)
Case "-"
Test = CStr(varInput1 - varInput2)
End Select

End Function

 

[JMB]

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.

 

[Jerry W. Lewis]

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]

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.