The value in col B is entered from DTPicker2 on a userform formatted as dpCustom hh:mm:ss tt. [....] Every time you type in Now() in a cell it reads like this: 12:50:24 PM, which I just typed into a cell, formatted as time. This tells me that Excel reads time values to the second, not millisecond, unless you know something I don't about the Windows system clock and its relationship to Excel's Now() function. Please enlighten me.
NOW() is specified to 10 decimal places but displayed to the nearest second by default
It might help to clear up some misunderstandings.
No, time is not "specified to 10 decimal places". It is just that Excel formats and displays only up to 15 significant digits.
There is an important difference between
VBA Now() and
Excel NOW().
VBA Now() returns time rounded down to the second.
Excel NOW() returns time rounded down to the 1/100 second (multiples of 10 milliseconds).
But when Excel and VBA format time as h:m:s or h:m, fraction seconds are rounded. For example, the time 12:34:5
6.789 formatted as h:mm:ss is formatted as 12:34:5
7 .
The Windows system clock is updated (a "tick") every 15.625 milliseconds, usually. Applications can alter that frequency, and some do. The effect is system-wide. So it is prudent to terminate all extraneous applications.
VBA Timer returns time of day in seconds and fractional seconds. Since it is type Single, not Double, time is accurate to 1/128 second or better, depending on the time of day. (And depending on an adjustment factor that the system clock appears to add, presumably to account for time drift.)
These facts can be demonstrated with the following VBA function:
Rich (BB code):
Function tod() As Variant
Dim xlnow As Double, vbatimer As Double, vbanow As Double
Dim res(1 To 3, 1 To 2) As Double
xlnow = [now()]
vbatimer = Timer ' type Single converted to Double
vbanow = Now
res(1, 1) = vbatimer / 86400
res(1, 2) = vbatimer
res(2, 1) = xlnow
res(2, 2) = (xlnow - Int(xlnow)) * 86400
res(3, 1) = vbanow
res(3, 2) = (vbanow - Int(vbanow)) * 86400
tod = res
End Function
Select B2:C4 and
array-entered (press
ctrl+shift+Enter instead of just Enter) the formula =tod() . Format B2 as [hh]:mm:ss.000 . Format B3:B4 as m/dd/yyyy hh:mm:ss.000 . Format C2:C4 as Number with 15 decimal places.
Function tod() is purposely not "volatile". Press ctrl+
alt+f9 to recalculate A1:B3.
In one example, the results are:
| A | B | C | D |
| 1 | | Date + TOD | TOD seconds | TOD seconds (exact) |
| 2 | VBA Timer | 09:14:45.355 | 33285.355468750000000 | 33285.35546875 |
| 3 | Excel NOW | 7/28/2019 09:14:45.350 | 33285.350000043300000 | 33285.3500000433,487002737820148468017578125 |
| 4 | VBA Now | 7/28/2019 09:14:45.000 | 33285.000000055800000 | 33285.0000000558,429746888577938079833984375 |
<tbody>
</tbody>
Column D shows the exact TOD in seconds, using my own formatter. I use period for the decimal point and comma to demarcate the first 15 signficant digits.
The infinitesimal differences in columns C and D are due to anomalies of the internal 64-bit binary floating-point respresentation. In a nutshell: time of day is stored as a fraction of a day; most decimal fractions cannot be represented exactly; and the binary approximation of a particular decimal fraction varies depending on the magnitude of the number.
Rich (BB code):
' Option Explicit
Dim MyRunTimer As Date
col headers A = Date B = Time C = Message
Sub MyMacro()
MyRunTimer = Now + TimeValue("00:00:10")
Dim i As Variant, t As Date, s As String
If Range("D1") <> "" Then
Exit Sub
Else
For i = 2 To [A65536].End(xlUp).Row
If Cells(i, 1).Value = DateValue(Now) Then 'if col A is today's date - works great
If Cells(i, 2).Value = TimeSerial(Now) Then
MsgBox "Both date and time values of current date and time match"
Else
MsgBox "code wrong"
End If
Next
Application.OnTime MyRunTimer, "MyMacro"
End If
End Sub
The posted code contains several fatal errors, highlighted in red: missing single-quote for the comment "col headers..."; TimeSerial(Now) should be TimeValue(Now); and missing EndIf, presumably before Next.
It is unclear to me how time values are entered into Cells(i,2). You say that you use DTPicker2, which returns time in the form hh:mm:ss tt
[sic] (presumably representing tt/100 seconds). I am not familiar with DTPicker2. But that is not a valid numeric Excel time form (missing period between "ss" and "tt"). I don't know if that is just another posting typo.
Ostensibly, the comparison Cells(i,2) = TimeValue(Now) might be changed to
Format(Cells(i,2),"h:m:s") = Format(Now,"h:m:s") .
But as noted above, formatting Cells(i,2) might result in a false difference if fractional seconds are rounded up.
I suspect the following will work:
Dim t As Double
t = Time
If
Int(86400 * Cells(i,2)) <> Int(86400 * (t - Int(t))) Then
It seems to be reliable, based on a brief test. But I worry about possible binary arithmetic anomalies.
-----
Aside.... There is no reason to use DateValue(Now) and TimeValue(Now). Simply use Date and Time, respectively.
However, if you might execute the code near midnight, it would be more reliable to capture date and time of day once, either before or within the loop, depending on your intentions (which are not clear to me). For example:
Dim n as Double, d as Double, t as Double
n = Now
d = Int
t = n - Int
d is the date. t is the time of day.
-----
PS....
Instead of nested "If" statements, I suggest
effectively the following (correcting the time comparison):
If Cells(i, 1).Value = DateValue(Now)
And Cells(i, 2).Value = TimeSerial(Now) Then
That would eliminated the syntax error due to a missing End If.
(But perhaps the broken code that you posted was an attempt to isolate the failure.)
