Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |