Date Comparison in Code

[nbs]

Having a problem with a date comparison...

c1 = 9/21/04 9:00:00 AM
d1 = 9/15/04 11:00:00 AM

If date (c1) > (d1) then UserForm1.Show

This should show the form, but instead because 11 is
greater than 9 it does not. And, when you reverse the
formula is will show the form.

If date (c1) < (d1) then UserForm1.Show

Is there a setting or reclaration required?

Thanks,
Noel

 

[Tom Ogilvy]

If the dates are stored as dates, then from the immediate window

? Range("C1").Value
9/21/2004 9:00:00 AM
? Range("D1").Value
9/15/2004 11:00:00 AM
? range("C1") > Range("D1")
True

even as Strings

? "9/21/2004 9:00:00 AM" > "9/15/2004 11:00:00 AM"
True

But if I convert the first to a date and compare to the second as a string
I get your result.

? cdate("9/15/2004 11:00:00 AM") > "9/15/2004 11:00:00 AM"
False

Your code as shown wouldn't be correct in either Excel as formulas or VBA as
code, so it is hard to say what you are doing, but with Date only prefacing
the first value, it doesn't appear correct technically or conceptually.

 

[Guest]

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