Date Filter is good, but still with limitation

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. Excel Tips - To filter a particular date ignoring years 1

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 

Excel Tips - To filter a particular date ignoring years 2

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

Excel Tips - To filter a particular date ignoring years 3

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.

Excel Tips - To filter a particular date ignoring years 4

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.

Excel Tips - To filter a particular date ignoring years 5

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

Excel Tips - To filter a particular date ignoring years 6

What do you think?

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

This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s