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

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


"Pete Fraser" wrote:

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

"JMB" wrote in message
...
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).



"Pete Fraser" wrote:

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

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

I count more than 9 digits.

"Pete Fraser" wrote:

"JMB" wrote in message
...
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).



"Pete Fraser" wrote:

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

"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

Format as hh:mm:ss.000
--
David Biddulph

"Pete Fraser" wrote in message
...
....
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.

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

"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

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