Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

enter image description here

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.

enter image description here

After running the filter, I get this:

enter image description here

share|improve this answer
    
This works great and is a really awesome answer! Thank you Kaze. I appreciate it. –  jftuga Sep 14 '11 at 19:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.