This is about how to filter a particular date, say 1st of January regardless year, from a list of dates.

As a matter of fact, not many people are aware of the Date Filter feature in Excel.  This is true at least in my working environment… 😛 Starting from Excel 2007, Excel groups date into a hierarchy of Year/Month/Date in Filter, provided that data of the whole column is input correctly as Date, but not text, and with no blank.  This is only one of the advantages of having date input correctly in Excel.

And the Date Filters actually provides many handy options for you to filter date range that meet your need.  For example, if you wanna show all dates in January ignoring years:

Date Filters –> All Dates in the Period –> January 

If you wanna show all 1st days ignoring months and years:

Click the drop down next to Search Box –> Select Date –> Input 01 in the Search Box –> OK

Note: 01 (Zero One) is needed.  If you input 1 (one) only, you will see a list of dates containing 1, i.e. 01, 10, 11, 12…, 21, 31. Nevertheless, there is NO option for showing a particular date, say 1st of January regardless years.

Now it’s time to workaround.

1) With Helper column

• In C2, input the formula =TEXT(A2,”MMDD”), then copy down
• Now you are ready to Filter “0101”, which means 1st of January, in Column C.

2) Without helper column but at the expense of date grouping feature

• Go to File –> Options –> Advanced –> Scroll down to the middle to locate the “Group dates in AutoFilter menu –> Uncheck this box –> OK
• Go back to the Filter and see the effect.

Now you may input 01/01 in the search box directly.

What do you think?

Do you have any tricks for Date Filter? Please share with us in comments.