On Mar 17, 6:38*am, Bill Sharpe 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. 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?
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.
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].
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.)
CAUTION: This macro overwrites A1:K3 of the current worksheet, 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,739228630071938619394700253 9061941206455230712890625
0.01/86400 =
0.000000115740740740740,74187560803210830795428876 17208180017769336700439453125
0.001/86400 =
0.0000000115740740740740,7385668855818961972548031 26952538150362670421600341796875
1/86400 =
0.0000115740740740740,7349934653356671176993586414 0279591083526611328125
(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.