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

chazrab · Jul 26, 2019

This code

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

Code:

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

Yongle · Jul 27, 2019

Could it be that time has simply moved on (even if only by a millisecond) by the time the code is testing the value with NOW() and therefore never matches EXACTLY

What is the value in column B and how does it get there ?

chazrab · Jul 27, 2019

Yongle said:
Could it be that time has simply moved on (even if only by a millisecond) by the time the code is testing the value with NOW() and therefore never matches EXACTLY

What is the value in column B and how does it get there ?

Hi Yongle - good question. The value in col B is entered from DTPicker2 on a userform formatted as dpCustom hh:mm:ss tt.

As for the time millisecond question, then how would you be able to compare a cell value of time in a column range that has a future time, with Now() through a repeating macro, and if a match is found, display a MsgBox on the screen?.

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. Thx for all your help. As you can see, all I'm wanting to do is simply enter a correctly formatted time value for a future time and when Now() is reached, displays a message. But it has to match the time to Now() first and exactly.
cr

Yongle · Jul 27, 2019

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

Try putting =NOW() into a cell and then formatting that cell as a number to 15 decimal places. You will get something like this:
43673.821011342600000
43673 is 27 July 2019
0.8210113426 is the time element

Iif all you care about is that a particular time has elapsed why not test if TIME in cell is GREATER than NOW()
or LESS than NOW() if that is the correct test for what you want

I will test a few ideas and reply to your question tomorrow

chazrab · Jul 27, 2019

Yongle said:
NOW() is specified to 10 decimal places but displayed to the nearest second by default

Try putting =NOW() into a cell and then formatting that cell as a number to 15 decimal places. You will get something like this:
43673.821011342600000
43673 is 27 July 2019
0.8210113426 is the time element

Iif all you care about is that a particular time has elapsed why not test if TIME in cell is GREATER than NOW()
or LESS than NOW() if that is the correct test for what you want

I will test a few ideas and reply to your question tomorrow

Thanks - I'm looking forward to hearing back from you on this.
cr

Yongle · Jul 28, 2019

1. Perhaps one of these two methods will give you what you want

2. "Date and time" tests combined into a single line (or use separate line to test date and time separately as per your original code)
- there are 2 separate tests which yield the same result - one numerically achieved and the other by building text string
- modify tests to suit the result required by you (code below ignores seconds)

3. The worksheet below illustrates by formula what the code returns.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][th]

H

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]Date[/td][td]Time[/td][td=bgcolor:#E2EFDA]

28/07/2019 13:00

[/td][td]Numerical
Test[/td][td]Formula in D2 copied down[/td][td][/td][td]TEXT
Equiv[/td][td]Formula in G2 copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

27/07/2019

[/td][td]