Advance Filter
Posted by Larry Shuler on June 06, 2001 4:27 PM
I have a list that could be 5000 rows deep. It is 20 columns wide. One column is labeled "Date Received". This column contains a date formatted as dd-mmm-yy. I want to do an Advance Filter and pull out each row that has a certian month in the date, ie, Jan, Feb, Oct, regardless of the day or year. I can't seem to do it, the wild card character option does not appear to work with dates. Any suggestions?
Posted by Russell on June 06, 2001 4:41 PM
What I would do is add a column that returns the month of your date column (with a formula like =Month(A1), where column A holds your dates). The formula will list the month number. Then you could filter on that number (or, you could also write a function or add another column that returns the NAME of the month and filter on that).
Hope this helps,
Russell
Posted by Mark W. on June 07, 2001 9:27 AM
Use a computed criteria...
If your first 'Date Received' is in cell A2 and
you want to find all the dates in the month of
February, then create a computed criteria by
entering the formula, =MONTH(A2)=2, into cell D2.
On the Advanced Filter dialog enter a "Criteria
range" of $D$1:$D$2. For more on criteria
construction see the Excel Help topic for
"Examples of advanced filter criteria".
Posted by Larry on June 07, 2001 2:45 PM
Re: Use a computed criteria...
I tried this one, Mark, and it did not work, to make sure, I just went back and tried it again. Thank you for sending it though, it looks like it should work.???
Posted by Mark W. on June 08, 2001 7:48 AM
Re: Use a computed criteria...
It does work! So the only question now is... why
not for you? I've gotta ask... did you leave the
1st row of the computed criteria blank? Ordinarily,
you'd put the field name in the 1st row, but NOT for
computed criteria!
Posted by Larry Shuler on June 08, 2001 11:36 PM
Re: Use a computed criteria...
Well, I will be damned. That works. I never would have guessed to leave the column header out of the criteria. Thank you so very much, you just made my day a little easier.
