How to filter dates by month with ignoring year in Excel?
Managing data with date entries is a common task in day-to-day spreadsheet work. For example, if you track daily expenses or activities spanning multiple years, you may sometimes need to analyze or filter your data based on a specific month, irrespective of the year the event occurred. However, Excel’s default filtering options prioritize years, making monthly analysis across multiple years less straightforward than expected. This tutorial discusses several practical approaches for filtering dates by month (ignoring the year) and helps you choose a method suited to your needs and Excel experience.
Filter dates by month ignoring year with Filter Function
Filter dates by month ignoring year with Kutools for Excel
Filter dates by month ignoring year with Excel Formula (Helper Column)
Filter dates by month ignoring year with Filter Function
By default, Microsoft Excel groups dates by year when applying filters. This can be limiting if your goal is to focus on all entries for a particular month, regardless of the year. In the filter drop-down menu 
, you generally see years categorized for selection. Fortunately, the Date Filters feature in Excel offers additional styles for date-based filtering, including the option to focus on months independently of their years.
The Expenses table below is an example dataset. You can filter the dates by month while ignoring the associated year using the steps outlined below:
1. Select the data column containing your dates. In this case, click Column B, which contains the date values you want to filter.
2. On the Data tab, locate and click the Filter button. This adds filter arrows to the header row of your table.
3. Click the filter arrow next to the header of your date column. Next, navigate to Date Filters > All Dates in the Period, and choose the month you want to view (e.g., May). This way, Excel will collect all dates falling within the chosen month, regardless of what year they are from.
4. Excel will now filter the dataset to show only the entries from the month you selected, spanning all available years. For example, all May records are displayed together for analysis or review as shown below:
This method is suitable when your dates are formatted as recognized Excel date values. If your dates are stored as text or in an unusual format, you may need to first convert them to date values or use a formula approach as explained below.
Potential issues include incorrect filtering if dates are not recognized as date types; always verify the cell formatting before applying date filters.
If you wish to revert to showing all records, simply clear the filter applied to the column.
Filter dates by month ignoring year with Kutools for Excel
Kutools for Excel is an Excel add-in that enhances built-in functionalities and provides advanced filtering, sorting, and data management capabilities. Free to try with no limitation for30 days. Get it Now.
Using the Kutools for Excel Super Filter feature, you can filter dates based on a specific month quickly, even when dealing with large or complex datasets. Here is how to use this method:
1. Go to the Kutools Plus tab and click on Super Filter. You will see a dialog box as shown below:
2. In the Super Filter dialog, configure the filtering options as follows:
- (1.) Click the
button to select the desired data range to filter. - (2.) If you have more than one filtering criterion, select the corresponding Relationship (AND/OR) as necessary.
- (3.) In the Group condition section, click the black line next to And to define your criteria, such as specifying "Month is equal to 5" for filtering all May entries, regardless of year. You can further set up other criteria as needed.
button to select the desired data range to filter.
3. Click the Filter button. The data is immediately filtered to display only entries whose dates fall within the target month, over any year. The results pane will instantly reflect the filter as seen below:
Notes:
1. To clear the applied filter and display all original data, simply click the Clear button in the Super Filter dialog.
2. The Super Filter tool also allows you to combine multiple conditions for complex filtering and supports saving frequently-used filter settings for future use.
Make sure your date column is properly formatted as date values to ensure precise filtering. For more information and advanced techniques, see Super Filter documentation.
Filter dates by month ignoring year with Excel Formula (Helper Column)
Adding a new column with a formula to extract the month number is a flexible way to group, filter, or sort dates by month, regardless of year. This approach is particularly helpful if you want more control over filtering logic or if your dates are not formatted for Excel’s built-in date filters. It is also effective if you want to perform additional calculations by month.
Applicable scenarios: Suitable for all versions of Excel, especially when you want to customize filtering or if operating on datasets where dates are in a standard date format. Also, this formula approach is helpful when working with exported data where the default filter grouping is not available or not granular enough. The downside is that it requires adding helper columns and some manual configuration, but it provides clear, step-by-step filtering by month across years.
1. Insert a new column next to your date column and add a header such as "Month".
2. In the first cell under this new header (assuming your date column is B and your first row of data starts at row 2, so the new cell is C2), enter the following formula to extract the month number from the date:
=MONTH(B2)This formula returns the month (1 for January, 2 for February, etc.) from each date in column B.
3. Press Enter to calculate the formula. Then, drag the fill handle down to copy the formula to all rows in your table for which you have date entries.
4. Now, with your "Month" helper column filled, use Excel's standard filter drop-down (or the Sort & Filter features) to filter for just the desired month number (e.g., filter for "5" to view all May values).
If you need to display month names, you can use an alternative formula such as:
=TEXT(B2,"mmmm")which will display "January", "February", etc., for easier recognition. Remember to adjust the exact cell and column letters if your layout differs. Verify that date values are not stored as text—otherwise, MONTH() may return errors.
Troubleshooting tips: If you see a #VALUE! error, double check that the cells in the date column are valid date types. For text dates, use =MONTH(DATEVALUE(B2)) after converting them as needed.
Related Articles:
How to sort birthdays (dates) by month in Excel quickly?
How to filter data via multiple criteria in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in