AutoFilter Criteria in Excel

[gremlins]

Is there any way I can perform a filter on a date value in a column of cells with reference to today's date?

For instance, if I wanted to filter on just today's date I could write:

strSearchDate = Date
Selection.AutoFilter Field:=17, Criteria1:=strSearchDate

but what I want to filter on is all the dates in column 17 that are 2 months or more prior to today's date. So as today is 01/07/04 I want to return any dates that are 01/05/04 or before.

The date format for column 17 is 14-Jun-04 so I can either convert today's date (strSearchDate) to this format or reformat all the cells prior to the filter.

Cheers

 

[PHV]

Have you tried something like this ?
Criteria1:="<=" & DateAdd("m", -2, Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Loomah]

Hi
this should do the trick

Criteria1:="<=" & edate(Now, -2)

note that you'll need to reference the analysis toolpak for vba

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244