MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Date filtering grief...please help!


Posted by seekwhence on March 09, 2001 11:55 AM

I'm just blown away by excels inability to filter by month. I have a column composed of dates formatted like this: March 14, 1999. All I want to do is filter by month (March in my example). I've tried the custom option in autofiltering as well as advanced filtering techniques but all I get is a blank return. I can't imagine this is impossible in excel so I must be overlooking something. Can someone please advise me how to accomplish this seemingly simple task?
TIA.


Posted by Mark W. on March 09, 2001 12:50 PM

> I'm just blown away by excels inability to
> filter by month.

Don't be quite so quick with your conclusions...

Suppose cells A1:A4 contain {"Date";36892;36923;36951}.
These numeric values are the date values for
1/1/01, 2/1/01 and 3/1/01. To filter on February
first setup a computed criteria by entering the formula,
=MONTH(Date)=2, into cell C2 and then use an Advanced
AutoFilter. In the Advanced Filter's Criteria range
field enter the reference, $C$1:$C$2, and press
OK.

Posted by Mark W. on March 09, 2001 12:55 PM

Of course, you could also...

Format your dates as MM or MMM and choose the desired
month from an AutoFilter drop down list.

Posted by seekwhence on March 09, 2001 8:48 PM

Thanx for the advice Mark. I tried your suggestion but I couldn't get it to work. (Upon enacting the filter, I get back no rows even though clearly there are cells containing the months I filtered for.)

Posted by Mark W. on March 09, 2001 10:52 PM

I guess we need to see a sample of your data.