Excel 2007 Advanced Filtering

Question

I need to filter multiple columns with dates. For example, I have 7 rows of names and the columns contain the dates they had office visits. So I want to be able to see the visits that occurred in a specific month. For example, I want all the visits to show that occurred in June which are listed in multiple columns.

Column A would have the name, Column B-E have other data about this person, Column F-I each have dates. If any cell in Column F-I contains a date falling in the specified month, I want the filter to show only those rows.

Can I do this with an advanced Excel Filter? If not, how can I do this?

Answer 1

Yes, it's possible with Advanced Filtering.

First, make sure your columns (even the dates) have unique headers (see image below). The yellow cell contains a formula that serves as the Criteria for the Advanced Filter:

=OR(MONTH(F6)=$B$2,MONTH(G6)=$B$2,MONTH(H6)=$B$2,MONTH(I6)=$B$2)

It returns TRUE if a row contains a date whose month is equal to the month number entered in $B$2. You can use custom number formats and conditional formatting to make this appear in "mmmm" format. You could also modify the formula above to take the name of a month instead of its number -- perhaps something like this:

=OR(TEXT(F6,"mmmm")=$B$2,TEXT(G6,"mmmm")=$B$2,TEXT(H6,"mmmm")=$B$2,TEXT(I6,"mmmm")=$B$2)

where $B$2 contains a validation list from which the user can select "January", "February"..."December".

In both formulas, F6, G6, H6 and I6 point to the first values in the date columns. These must be relative cell references in order for the filter to work.

Make sure that the Criteria cell (yellow) has an empty cell above it. To run the filter:

1. Select your data table.
2. Go to Data > Advanced
3. Select Filter the list, in-place
4. Make sure that List range contains the reference to your data table (including headers).
5. For Criteria range, select the Criteria cell (yellow in my example) AND the empty cell above it.

After running the filter, I get this: