Re: Time in format hh:mm:ss.milliseconds
- From: joeu2004 <joeu2004@xxxxxxxxxxx>
- Date: Fri, 6 Mar 2009 08:34:21 -0800 (PST)
PS....
On Mar 6, 8:23 am, I wrote:
But 0:0:0.001 is represented by exactly
0.0000000115740740740740,755110497832956750752231300793937407433986663818359375.
[...] When that is multiplied by 86400000, we get exactly
1.00000000000000,02220446049250313080847263336181640625.
[...] the difference of 2.22E-16 might adversely impact
some computations.
But there should be no problem if the OP is maintaining milliseconds
as integers and simply using the format to display the number. That
is how I work with milliseconds (and less).
----- original posting -----
On Mar 6, 8:23 am, joeu2004 <joeu2...@xxxxxxxxxxx> wrote:
On Mar 6, 6:10 am, "Bernard Liengme" <blien...@xxxxxxx> wrote:.
Why the worry about Excel's precision? A millisecond is
only about 1.64 × 10^5 of a day.
A millisecond is about 1.1574E-08 of a day. To be more precise, it is
1/86400000 of a day. The denominator is 24*3600*1000.
You can confirm this by formatting a cell with the custom format
hh:mm:ss.000, entering 0:0:0.001, then reformatting the cell as
scientific with 4 or more dp.
I would have thought Excel could cope.
Or am I missing something?
A couple things. But before I get into details, let me say that it
does not seem to make much difference. When I multiply 0:0:0.001 by
86400000 (and format the cell as d:hh:mm:ss.000), I get
1:00:00:00.000, which Excel says equals 1.
What you are missing is: anytime we deal with decimal fractions that
cannot be represented exactly as a sum of a finite number (up to 53)
powers of 2, there is room for "numerical error". I'm sure you know
that.
Even in decimal, 1/24 is a repeating fraction, and so is 1/3600. So
you can imagine that 1/24/3600/1000 cannot be represented exactly with
a finite number of digits in decimal, much less binary.
In decimal, a millisecond is 1.15(740)...E-08 of a day, by which I
mean that 740 is repeating.
In 64-bit floating-point, 1/86400000 is represented by exactly
0.0000000115740740740740,738566885581896197254803126952538150362670421600341796875.
(The comma demarcates 15 significant digits to the left.) An
infinitesimal "error", to say the least. In fact, if we multiply that
by 86400000, we get exactly 1. The same is true if I use
1/24/3600/1000 and multiply by 24*3600*1000. (Note: Technically, the
order of those factors could make a difference. But I don't think it
does in this case.)
But 0:0:0.001 is represented by exactly
0.0000000115740740740740,755110497832956750752231300793937407433986663818359375.
Notice the difference after the comma, a slightly greater "numerical
error". When that is multiplied by 86400000, we get exactly
1.00000000000000,02220446049250313080847263336181640625. In an IF()
comparison, Excel considers that equal to 1. But the difference of
2.22E-16 might adversely impact some computations.
----- original posting -----
On Mar 6, 6:10 am, "Bernard Liengme" <blien...@xxxxxxx> wrote:
Pete-
Why the worry about Excel's precision? A millisecond is only about 1.64 ×
10^5 of a day. I would have thought Excel could cope. Or am I missing
something?
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email
"Pete_UK" <pashu...@xxxxxxxxxxx> wrote in message
news:e1620d71-e2be-42cf-8c49-bde2965faacf@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Set a custom format of:
hh:mm:ss.000
I'm not sure how accurate those readings will be though, due to
Excel's floating point restrictions - perhaps you could import the
field as text and then extract the milliseconds into a different
column.
Hope this helps.
Pete
On Mar 6, 11:04 am, Angus <anguscom...@xxxxxxxxx> wrote:
Hello
I have a csv file with a time field in the format:
hh:mm:ss.milliseconds - eg 10:38:27.686
In Excel it does not display correctly. I tried to setup a custom
time format hh:mm:ss.ms - and I think it sees ms as a combination of
minutes and seconds - so that didn't work. I need to see the
milliseconds. Also I will be charting this field and applying
formulae to it so presumably I need to get Excel to recognise it
properly.
How can I set the correct format for this time field?
Angus
- Follow-Ups:
- Re: Time in format hh:mm:ss.milliseconds
- From: Pete_UK
- Re: Time in format hh:mm:ss.milliseconds
- References:
- Time in format hh:mm:ss.milliseconds
- From: Angus
- Re: Time in format hh:mm:ss.milliseconds
- From: Pete_UK
- Re: Time in format hh:mm:ss.milliseconds
- From: Bernard Liengme
- Re: Time in format hh:mm:ss.milliseconds
- From: joeu2004
- Time in format hh:mm:ss.milliseconds
- Prev by Date: Re: Picture1_click
- Next by Date: automatic pivot tables
- Previous by thread: Re: Time in format hh:mm:ss.milliseconds
- Next by thread: Re: Time in format hh:mm:ss.milliseconds
- Index(es):
Relevant Pages
|
