Matching a time value in a cell to read the correct value of Now...

[chazrab]

This 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

This macro just repeats itself and checks col A , col B and col C every 10 seconds
to see if a new value of Date, Time and Message has been entered.

When an entered value in col A and B matches Now, a Msgbox displays
saying so.("Date and TIme value of Now found". Col A is formatted as Date mm/dd/yyyy, col B is formatted as Time(1:30 PM) and C as string.

I can't figure out how to tell Excel to read the correct Time value in col B as
the current time and display the message. Excel reads the correct Date
for Now with this code perfectly every time

Cells(i, 1).Value = DateValue(Now)

Can someone please help with the correct code for telling Excel the value in
col B is the correct time. It's either with the code line for reading the time correctly 'or the formatting or both.

Thanks for anyone's help.
cr

 

[chazrab]

Hi Yongle - I need to understand your code as much as you do. So I am studying it now. My plan is to copy your code to module in a new workbook, insert your same list of data, and run it so I can see what happens. From what I can see in your code, it looks like it works correctly, but I need to educate myself in understanding how.
D_T = Cells(i, 1) + Cells(i, 2) seems like it's a combined Date and Time value, not the sum of the two.
Events will match NOW on the same day at different times and on different days and different times, just like your sample code seems
If N - D_T > 0 And N - D_T < M Then seems like it is the code line that finds both a date ant time match with NOW, and then displays a msgbox.

This is about as far as I've gotten so far.

I think I may have mentioned this b4, but what if the user is not present to close the userform that is displayed after the first match? They certainly can't be in a "wait" state can they, waiting to display as soon as each earlier message is closed ? When I tried this, an error message was generated because the Application.onTime Proc was in use.

Please let me know if my logic is correct in all this. You've spent a lot of time on this and I certainly appreciate it.
Thx for all your help.
cr

 

[joeu2004]

I apologize if my response was TMI. I was trying to answer your questions, reflected in comments like ``Excel reads time values to the second, not millisecond`` (incorrect) and ``something I don't [know] about the Windows system clock and its relationship to Excel's Now``.

Previously, you wrote that ``col B is entered from DTPicker2 on a userform formatted as dpCustom hh:mm:ss tt``. I interpreted to mean that time was stored accurate at least to the second (ss), and perhaps to the 1/100 second (tt?).

Now you say that you use DTPicker2 ``to enter time as 1:30 PM in col B``, which is accurate to the minute, not seconds or fractions of a second.

If DTPicker2 returns time accurate only to the minute, one issue is that you are trying to compare that for equality with time that is accurate to the second (VBA Now). They will never compare equal.

Since I am not familiar with DTPicker2, I have no idea how to interpret and resolve the contradiction. So I will not be able to participate further in this discussion.

 

[Yongle]

D_T = Cells(i, 1) + Cells(i, 2) seems like it's a combined Date and Time value, not the sum of the two

Date is an integer
1 Jan 1900 is Day 1 (in Excel)
29 July 2019 is Day 43675

Time is a decimal - part of day
18:00 = 18/24 = 0.75
18:10 = 18/24 + 10/(60 X 24) = 0.756944444

Sum the two is 6:10pm on 29 July 2019
0.756944444 + 43675 = 42675.756944444

To understand this properly try putting various dates and times into cells and then format cells as number to 5 decimal places, also try =TODAY() and = NOW()