Date Comparison in Code

[nbs]

Here is the actual code that causes this interesting
problem:

If Sheets("Monitor").Cells(6, 5).Value > Sheets
("Corrective Actions").Cells(6, 5).Value Then
UserForm1.Show

Both are date formatted fields as described below, yet it
seems to be only looking at the time of day.

Thanks for any further insight!!

 

[Ron Rosenfeld]

Here is the actual code that causes this interesting
problem:

If Sheets("Monitor").Cells(6, 5).Value > Sheets
("Corrective Actions").Cells(6, 5).Value Then
UserForm1.Show

Both are date formatted fields as described below, yet it
seems to be only looking at the time of day.

Thanks for any further insight!!

You wrote that you checked the formatting of the fields but you did write that
you checked the actual contents of the fields.

Even if the fields are formatted as date, it is still possible for the contents
of one (or both) of the fields to be TEXT. See what =ISTEXT(cell_ref) shows.


--ron

 

[Tom Ogilvy]

Another way to check is to do
Sub AAAA()
Dim vVal1 As Variant, vVal2 As Variant
Dim msg As String
vVal1 = Sheets("Monitor").Cells(6, 5).Value
vVal2 = Sheets("Corrective Actions").Cells(6, 5).Value
msg = TypeName(vVal1) & " " & vVal1 _
& vbNewLine & TypeName(vVal2) & " " & vVal2 _
& vbNewLine & "Monitor > Corrective: " & (vVal2 > vVal2)
MsgBox msg

End Sub

Suggest that you will get something like:

Date 9/21/04 9:00:00 AM
String 9/15/04 11:00:00 AM
Monitor > Corrective: False