Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing dates with code | Excel Discussion (Misc queries) | |||
Flawed scatter plot function | Charts and Charting in Excel | |||
Comparing Dates | Excel Discussion (Misc queries) | |||
comparing two dates and executing code on condition | Excel Discussion (Misc queries) | |||
Flawed Macro | Excel Programming |