Comparing Dates - Flawed Code?
 

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

--
br_turnbul
-----------------------------------------------------------------------
br_turnbull's Profile: http://www.excelforum.com/member.php...fo&userid=2747
View this thread: http://www.excelforum.com/showthread.php?threadid=47820

Comparing Dates - Flawed Code?
 

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.
--
- K Dales


"br_turnbull" wrote:


Afternoon all,

I have a macro that compares dates but no matter what i try it doesn't
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, but
none of the comparing IF statements work???

Any Help?

Thanks in advance!


--
br_turnbull
------------------------------------------------------------------------
br_turnbull's Profile: http://www.excelforum.com/member.php...o&userid=27479
View this thread: http://www.excelforum.com/showthread...hreadid=478200

Comparing Dates - Flawed Code?
 

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 wrote:

Afternoon all,

I have a macro that compares dates but no matter what i try it doesn't
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, but
none of the comparing IF statements work???

Any Help?

Thanks in advance!

--
br_turnbull
------------------------------------------------------------------------
br_turnbull's Profile: http://www.excelforum.com/member.php...o&userid=27479
View this thread: http://www.excelforum.com/showthread...hreadid=478200

--

Dave Peterson

Comparing Dates - Flawed Code?
 

Genius, thanks a lot, A+


--
br_turnbull
------------------------------------------------------------------------
br_turnbull's Profile: http://www.excelforum.com/member.php...o&userid=27479
View this thread: http://www.excelforum.com/showthread...hreadid=478200