Comparing Dates - Flawed Code?

[br_turnbull]

Afternoon all,

I have a macro that compares dates but no matter what i try it doesn'
seem to work.

Simply it goes a little like:

Dim cellDate 'Date that is in a certain cell
'format DD/MM/YYYY HH:MM.

cellDate = Cells(row, col) 'Varies depending on previous actions.

Dim formatDate 'cellDate formatted
'into same as i want to compare it to.

formatDate = Format(cellDate, "dd/mm/yyyy")

If formatDate >= "01/10/2005" Then

If formatDate <= "31/10/2005" Then

End If

End If

I have tried initialising the variable as dates, strings, variants, bu
none of the comparing IF statements work???

Any Help?

Thanks in advance

 

[Guest]

The problem is comparing the dates - now strings - with < and > comparisons.
What you now get is an alphabetic comparison of the strings, not a date-wise
comparison. For example, alphabetically the string "2" comes AFTER "10"
(just as "b" comes after "az").

You can do the formatting if you still need it for other purposes, but then
do the comparison as:
If DateValue(formatDate) >= DateValue("01/10/2005") Then...
If DateValue(formatDate) <= DateValue("31/10/2005") Then...
This explicitly converts them back to date values for the purposes of the
comparison.

 

[Dave Peterson]

dim CellDate as Date
celldate = int(Cells(row, col).value) 'remove the time

'dateserial(year,month,day)
if celldate >= dateserial(2005,1,10) then
'....
if celldate <= dateserial(2005,10,31) then


Comparing strings (the date formatted) can cause lots of trouble. Comparing the
actual date seems to work ok.

 

[br_turnbull]

Genius, thanks a lot, A+