VBA Date Format
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 |
VBA Date Format
Ignore the format, go with INT
if INT(dBD)int(dRBD) which will check the integer (and therefore DATE) part of the cell only |
VBA Date Format
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 |
VBA Date Format
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 |
VBA Date Format
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. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Kieranz" wrote: 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 |
All times are GMT +1. The time now is 02:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com