[jakester]

Jerry, you'll probably be the one to respond so I'll just post this for you.

I'm trying to compare some date values using VBA and the issue I'm having is that my code is treating date values that are older than more recent values as greater. So, if you were to run the code below you'd see that it's returning EOPM instead of BOCM. Well, that can't be right because Dec 31, 2010 is not a greater date value than 1/1/11.

I won't get into what I'm using this for or any of that because it would take me awhile to explain that and I don't have that kind of time. If you have any suggestions, I'd greatly appreciate it.

BOPM = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mm.dd.yy") ' Begin of Prev Month
EOPM = Format(DateSerial(Year(Date), Month(Date), 0), "mm.dd.yy") 'End of Prev Month
BOCM = Format(DateSerial(Year(Date), Month(Date), 1), "mm.dd.yy") 'Begin of Curr Month
EOCM = Format(DateSerial(Year(Date), Month(Date) + 1, 0), "mm.dd.yy") 'End of Curr Month
MRD = Format(Date - 1, "mm.dd.yy")

date1 = Format(Date - 1, "mm.dd.yy")
date2 = Format(Date - 2, "mm.dd.yy")
date3 = Format(Date - 3, "mm.dd.yy")

If BOCM > EOPM Then

MsgBox ("BOCM")

Else

MsgBox ("EOPM")

End If


End Sub

[ScottGem]

The problem is that you aren't comparing date values. By using Format, you are converting the date values to text strings. You should use Format only when you are ready to display the value.

[JBeaucaire]

Actually, Excel is pretty good, sometimes, at doing <> comparisons on text strings, too. I'd suggest that your non-standard format of "mm.dd.yy" is just too far to go. You might find that Excel gets it right if you use a standard date format like "mm/dd/yy", and almost 100% sure if you use the data format that matches your system's default date format.

[JBeaucaire]

Note, you can eliminate the whole issue by using Excel's VarType of Date. Declaring and using variables as dates removes any need to fiddle with formats of any kind, as Scott has already pointed out.

Using your example:

Dim BOPM As Date, BOCM As Date
Dim EOPM As Date, EOCM As Date, MRD As Date
Dim Date1 As Date, Date2 As Date, Date3 As Date

BOPM = DateSerial(Year(Date), Month(Date) - 1, 1)   'Begin of Prev Month
EOPM = DateSerial(Year(Date), Month(Date), 0)       'End of Prev Month
BOCM = DateSerial(Year(Date), Month(Date), 1)       'Begin of Curr Month
EOCM = DateSerial(Year(Date), Month(Date) + 1, 0)   'End of Curr Month
MRD = Date - 1

Date1 = MRD
Date2 = Date - 2
Date3 = Date - 3

If BOCM > EOPM Then MsgBox "BOCM" Else MsgBox "EOPM"