Re: Time Accuracy

See inline comments

joeu2004 wrote:
On Mar 17, 6:38 am, Bill Sharpe <wfsno...@xxxxxxxxxxxx> wrote:
My math was based on Chip's information that date/time information is
stored as an integer and decimal combination with six digits set aside
for the time component. That means you can slice 24 hours into 1,000,000
equal pieces. There are 24x60x60x10 = 860,000 tenths of a second in a
day. Therefore Excel can potentially track only to the tenths of a
second. The hundredths of a second and beyond will result in unreliable
numbers.

Your reasoning would be sound, if the information were correct. But
Chip's information is incorrect, at least in a literal sense.

First, I hasten to note that according to http://support.microsoft.com/kb/214094,
the format of the time component is just 5 decimal fractions.

And the reason KB214094 uses a five-digit representation for an elapsed time of 12 hours 45 minutes is because 0.53125 is the exact decimal value for that time period. 12.75/24 = 0.53125.

By your
reasoning, that would slice 24 hours into 100,000 equal pieces, and
the smallest increment of time would be 24x60x60 = 86400 seconds(!).
Who ya' gonna believe?

My reasoning was based on 1,000,000 slices of time each 24 hours per Chip's site. That means the 864,000 tenths of a second in one day can theoretically be handled by the available decimals. Anything beyond that (hundredths of a second, thousandths of a second is unreliable. If Chip is wrong then obviously my conclusion is wrong, but see below.

The answer is: neither is correct.

You can easily verify this by entering the time 0:0:0.001 and
formatting the cell as Number with at least 8 decimal places. (13 dp
is more representative.)

Although h:mm:ss.000 is the most precise custom format supported and
recognized for data entry, we can actually input any fractional time.
As always, the displayed value is subject to Excel's rounding rules.
For example, the largest number displayed as 23:59:59.999 is about
0.999499999999999/86400.

(FYI, KB 214094 asserts that 0.99999 is 23:59:59. It is true that
that value is displayed that way. But if we compare it with time
entered as 23:59:59, the comparison is false(!).)


The hundredths of a second and beyond will result in
unreliable numbers.

I am not sure what you mean by that.

See my comment above. Perhaps I should have said unreliable digits rather than unreliable numbers.

All decimal fractions and non-integer expressions are subject to the
oddities of IEEE floating point representation. But I would say that
the internal representation of 0.1 seconds is no less "reliable" than
the representations of 0.01, 0.001 and even 1 second expressed as
fractions of a day (i.e. divided by 86400), at least within 15
significant digits. In fact, I believe the internal representation of
0.01 seconds is closer to its infinite representation than 0.01
seconds, and both are closer than 1 second is(!) [1].

I'm not getting into the decimal system vs. the binary system and the loss of accuracy that entails at some point in working with very small numbers. Some fractions are exactly equal in decimal and binary. 1.1 binary exactly equals 1.5 decimal. 1.11 exactly equals 1.75 decimal. Most such conversions are approximate, although close enough for most practical purposes.


But all of that is about representation of time. The original posting
raised issues about the resolution of the Excel NOW() function.

I believe the following macro demonstrates that the smallest time
increment reflected by NOW() is 10 milliseconds. (Sometimes we see a
20-msec difference. There are for various possible reasons.)

Ten milliseconds is 0.01 seconds. And I noted above that the numbers beyond the tenths column are unreliable. That could explain the occasional 20 millisecond difference.

CAUTION: This macro overwrites A1:K3 of the current work***, and it
changes the column width of A:K.

Private Sub measXLNow()
Dim n As Long
Dim st As Double, et As Double
Dim c As Integer
Dim oldCalc

Application.ScreenUpdating = False
oldCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Range("A1") = "start Now()"
Range("A2") = "end Now()"
Range("A3") = "#loops"

For c = 1 To 10
Range("B1").Cells(1, c).Formula = "=Now()"
st = Range("B1").Cells(1, c)
n = 0
Do
n = n + 1
Range("B2").Cells(1, c).Formula = "=Now()"
et = Range("B2").Cells(1, c)
Loop Until et <> st

Range("B1").Cells(1, c) = st
Range("B1").Cells(1, c).NumberFormat = "h:mm:ss.000"
Range("B2").Cells(1, c) = et
Range("B2").Cells(1, c).NumberFormat = "h.mm:ss.000"
Range("B3").Cells(1, c) = n
Range("B3").Cells(1, c).NumberFormat = "0"
Next c

Range("A1", Range("B3").Cells(1, c)).Columns.AutoFit
Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub


Endnotes:

[1] 0.1/86400 =
0.00000115740740740740,7392286300719386193947002539061941206455230712890625
0.01/86400 =
0.000000115740740740740,7418756080321083079542887617208180017769336700439453125
0.001/86400 =
0.0000000115740740740740,738566885581896197254803126952538150362670421600341796875
1/86400 =
0.0000115740740740740,73499346533566711769935864140279591083526611328125

(The comma demarcates 15 significant digits to the left.)

[2] All of my examples were derived on MS Win XP using Excel 2003 and
VB 6.3.

Neat macro!

The repetition of the digits "740" four times before the comma in the examples above( and almost five times as the next sequence after the comma is 739, 741, 738, and 734) is suspect, although I guess no more so than the endless threes in the decimal representation of 1/3. I'd be less suspicious if the 740's kept on repeating.

I just had an "aha" moment. If I store just the time in a cell, e.g. 12:01:01 PM, Excel displays the time but stores the information as 0.500011574074074 -- fifteen digits and there's those suspicious 740 digits repeating again in there. If I add today's date and store the cell formatted as date and time, Excel stores the information as 39889.5000115741 -- five integer digits and ten decimal digits. Apparently Excel does keep track of time to more than six decimal digits.

I'm still curious to know why the OP wants to be so precise in Excel.

And it's not nearly close enough to dinner time -- corned beef tonight!

Bill
.

Loading