advanced filter issue

[Joseph Atie]

Im trying to filter a data set on 3 criteria

project name, start date, end date

the project name part is fine the problem im having is that im getting dates
outside of my date range

im using the following

start date <=21/02/2009 & end date >=19/02/2009

im getting dates start after the start date, but not before

all three filter criteria are on the smae line, which to my understanding
means

project name & start date & end date

help please

 

[OssieMac]

Hi Joseph,

I'm confused by your start date and end date. "start date <=21/02/2009 & end
date >=19/02/2009". How can you have a project End date that is earlier than
the Start date?

 

[Joseph Atie]

lol no no if there is a list of 500 projects or tasks no 2 tasks start or end
at the same time but im looking to collect tasks that are actually operation
between the start & end dates

so if the start date is <= end date add it

and if the end date is >= start start date add it to the list.

 

[OssieMac]

Hi again Joseph,

I am assuming that you are using Autofilter. If so, then when the Start Date
filter is set, it will include everything =< 21/02/2009 in the Start Date
column. Now when you set End Date it will include everything that is
=>19/02/2009 and hence you get dates outside the range.

I am still not sure if I am on the same wave length as yourself. But I think
that you probably need a Helper column and insert an If formula and then set
the filter on that column.

Somewhere out of the way on your worksheet you could insert your required
Start and End Date for the filter.

For the example I have assumed that:-
Your Start date column is B
End date column is C
Required Start date filter is in G1
Required End date filter is in H1
Helper column is D

The following formula is in cell D2. (Note absolute reference with $ signs
for the Start date and End date filters.

=IF(AND(B2<=$G$1,C2>=$H$1),"Include","Exclude")

Copy the formula down and call the column header Formula or something.

Now you should be able to set the filter to “Includeâ€

 

[Joseph Atie]

As stated in the title of the post im using advanced filter

my understanding is that if i have all three criteria on the same line for
the advanced filter that it is equivalent to the if statement you wrote above.

i.e

if project name = x and start date <=x and end date >=x then include else
exclude

is this correct?

if so why am i getting dates that exceed the start date limit

ie if i set the start date parameter to 21/2/09 i get dates up to the 30/2/09

 

[OssieMac]

Hi Jopseph,

I'm sure that we are on a different wave lengths. Perhaps you could post a
small sample of data and then indicate the data that you want included and/or
excluded by the filter.