Date Comparison

[nasar]

I need return rows from a list which satisfy a criteria based on the date field. My problem is that I cannot specify greater than (>) criteria.

My list contains the following fields:

Date Item
1-Nov-2002 AAA
2-Nov-2002 BBB
3-Nov-2002 CCC

I want to be able to choose Items that are > "2-Nov-2002". The equality check works fine, I just put 2-Nov-2002 in the criteria region, but > 2-Nov-2002 does nothing.

Any help would be appreciated.

 

[xlbo]

Is this within a filter / code ???
What exactly is not working ?? Rgds
~Geoff~

 

[ETID]

Try placing the greater than operator inside the quotes


IE


">2-Nov-2002"

 

[nasar]

At this stage I am using a DCOUNT function:

=DCOUNTA(A1:B4,2,AI1:AI2)

In cell AI1, I have the text "Date" and in cell AI2 I place my criteria.

Putting the criteria in quotes ">2-Nov-2002" does not work

 

[xlbo]

Could it be that your "dates" are actually text
put =TEXT(A1) where one of your dates is in A1 - if it returns true, your dates are actually text which would explain why it isn't working Rgds
~Geoff~

 

[nasar]

I tried =ISTEXT(A1) and it returned TRUE (but this is correct as this is the header) and returned FALSE for A2..A4

Also tried N(A1) and TYPE(A1), for the cells containing dates N() returned the date and TYPE() returned 1.

 

[sha76]

"2-Nov-2002" will make it treat your date as text. Try #2-Nov-2002#.

Sharon

 

[nasar]

Success, I tried >2-Nov-2002 (note, no space after >) and this worked.

I must have been putting a space after the > and therefore getting the problem.

Thanks for all your help guys, it is *really* appreciated.

 

[klmorgan]

On advanced filters and dcounts I have been using concatenate to build the date ie

In a1 have >
In a2 Have =
In a3 have the date

Then concatenate a1,a2,a3 to build >=Date

Keith