VBA Date Format

[Kieranz]

Hi MVPs
I have 2 date cells formatted as dd mmm yyyy hh:mm:ss in Excel 2003
one is budget date in D3 and the other revise budget date in D4.

I have a macro that reads:
Option Explicit
Sub Chkdate()
Dim dBD as date
Dim dRBD as date

dBD = range("D3").value
dRBD = range("D4").value

If dBD < dRBD then ' problem comparison by vba includes the time as
well instead of
do xyz ' dd mmm yyyy only.
endif

What i would like to do is
IF format(dBD,dd/mmm/yyyy) < format(dRBD,dd/mmm/yyyy) then
do xyz
I am not a fundi (expert) in vba
Thks and Rgds
KZ

 

[aidan.heritage]

Ignore the format, go with INT

if INT(dBD)>int(dRBD)

which will check the integer (and therefore DATE) part of the cell only

 

[Kieranz]

Hi Aidan
Didn't work suspect its because of time element. The idea is that the
variable must check the condition excluding the time element. ie if
24/May/2006 < 24/May/2006 then do xyz. But i suspect because the cells
have been formated with time the variable is checking with the time
element ie 24/05/2006 15:45:23 < 24/05/2006 16:00:10 and therefore
jumping to abc instead of xyz.
any help appreciated. Thks and Rgds KZ

 

[Kieranz]

Hi All
I tried the following and it seems to work
If FormatDateTime(dBD,2) < FormatDateTime(dRBD,2) then - ( 2 is
vbshortdate)
Thks and Rgds KZ

 

[Tom Ogilvy]

Just so your aren't scarred for life -
I can't say why it didn't work you (maybe an implementation error), but
demoing from the immediate window

? dt
05/26/2006 1:40:00 PM
? cdbl(dt)
38863.5694444444
? cdbl(int(dt))
38863

shows that Int strips the time portion off.

formatdatetime(dt,2) does as well

? formatdatetime(dt,2)
05/26/2006

But further checking shows that it returns a string
? typename(formatdatetime(dt,2))
String

doing a string comparison with that format can give faulty results

? "01/30/2006" < "10/01/2005"
True

the real question is why the original comparison shouldn't work. It should
give the correct answer whether time is involved or not if they are on
separate days. If you wish the same day to be exluded. you could use INT.