Time in format hh:mm:ss.milliseconds

[Angus]

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

 

[Angus]

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

I should have searched first

format to use is hh:mm:ss.000

 

[Pete_UK]

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

 

[Bernard Liengme]

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 MVP
http://people.stfx.ca/bliengme
remove caps from email

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

 

[joeu2004]

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 -----

 

[joeu2004]

PS....

But 0:0:0.001 is represented by exactly
0.0000000115740740740740,75511049783295675075223130079393740743398666381835­9375.
[...] 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 -----

 

[Pete_UK]

In a follow-up question the OP has asked how to get the milliseconds
in a separate column.

Pete

PS....

But 0:0:0.001 is represented by exactly
0.0000000115740740740740,75511049783295675075223130079393740743398666381835­­9375.
[...] 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 -----

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.

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,73856688558189619725480312695253815036267042160034­­1796875.
(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,75511049783295675075223130079393740743398666381835­­9375.
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 -----

- Show quoted text -

 

[Bernard Liengme]

I did the math wrong! 1/(24*60*60*1000) is indeed 1.1574E-8
So we are getting closer to the IEEE precision bar than I thought
thanks
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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 -----

 

[joeu2004]

I did the math wrong! 1/(24*60*60*1000) is indeed 1.1574E-8
So we are getting closer to the IEEE precision bar than I thought

Well, no closer than the result from 1/3 ;-). The point I was making
is: anytime we use decimal fractions, we run the risk of introducing
infinitesimal "numerical errors" (I prefer to call them abberations)
that can have side-effects down the road. Consider the following
example.

Enter 0:0:0.999 into A1 and 0:0:0.001 into A2, then compute =A1-A2 in
A3. A3 will display 0:0:0.998. Now, enter 0:0:0.998 into A4. Then,
IF(A3=A4,TRUE) returns TRUE, as we expect. But IF(A3-A4=0,TRUE)
returns FALSE (!). And the computational abberations can compound to
the point where even A3=A4 returns FALSE.

Similarly, IF(1-1/3=2/3,TRUE) returns TRUE, but IF(1-1/3-2/3=0,TRUE)
returns FALSE.

It's (usually) not the magnitude of the decimal fraction that matters,
but whether or not the decimal fraction can be represented exactly in
the binary floating-pointing form (and, if not, how close the binary
representation is).

(In those particular examples, Excel heuristics are to blame. For
example, 1-1/3 is just as different from 2/3 as 1-1/3-2/3 differs from
zero. The only reason why the two IF() expressions behave differently
is Excel's heuristics applied to the first IF() expression. But more
often, the side-effects are the result of binary computer arithmetic.)


----- original posting -----