Function Problems

How to Filter Excel Dates by Day and Month Across All Years

Khadija Khan Oct 22, 2026 868 views

Question details

The user needs to filter a list of dates to extract records matching a specific day and month, ignoring the year.

How to Filter Excel Dates by Day and Month Across All Years

Product: Excel
Device & OS: not provided
Scenario: Filtering date records, such as employee birthdays, anniversaries, or recurring annual events, where the year is irrelevant to the search.
Observed behavior: Standard date filters in spreadsheets group dates by year first, making it difficult to isolate the same day and month across multiple different years without a workaround.

Before you start

Ensure your date column is formatted as actual dates rather than plain text, and note the exact range of your data (e.g., A2:A7) before applying the formula.

Solution 1Recommended

Use the FILTER and TEXT Functions to Extract Specific Dates

Combine the FILTER function with the TEXT function to convert dates into a text string that isolates the day and month, allowing you to bypass the year entirely.

The FILTER function can extract rows based on specific conditions. By using the TEXT function inside it, we can temporarily format the dates to show only days and months (like "ddmm"), and then match that against our desired date.

Select an empty cell for your output

Click on a blank cell where you want the filtered list of dates to appear.

Enter the FILTER formula

Type the formula =FILTER(A2:A7,TEXT(A2:A7,"ddmm")="0106"). Make sure to replace A2:A7 with the actual cell range of your dates.

Adjust the target day and month

Change the "0106" portion of the formula to match the specific day and month you are looking for (e.g., "0106" represents the 1st of June in a Day-Month format).

Execute the formula

Press Enter. The cell and the area below it will automatically populate with all the dates from your list that match the specified day and month, regardless of the year.

Use the FILTER and TEXT Functions to Extract Specific Dates

Regional Format Settings: If your spreadsheet's regional settings use the Month-Day format natively, you may need to adjust the text format argument to "mmdd" and the target string accordingly (e.g., "0601" for June 1st).

Solution 2

Use a Helper Column and Standard AutoFilter

If your version of Excel does not support the dynamic FILTER function, you can use a helper column to extract the day and month, then apply standard filters.

Filter Data Efficiently with WPS Spreadsheet

Use WPS Office to Filter Dates Across Multiple Years Easily

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and TEXT. You can quickly extract recurring annual dates, manage large datasets, and analyze information without worrying about complex workarounds.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the list of dates.
2. Apply the Formula: Select a blank cell and input the formula =FILTER(A2:A7,TEXT(A2:A7,"ddmm")="0106").
3. View Filtered Results: Hit the Enter key. WPS Spreadsheet will instantly display all matching dates regardless of the year.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Supports advanced dynamic arrays like the FILTER function out of the box.Lightweight, fast, and optimized for handling large datasets efficiently.Free to use with a clean, familiar tabbed interface to boost productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Can I filter dates by just the month without specifying the day?

Yes. You can use the MONTH function inside the FILTER formula. For example, =FILTER(A2:A7, MONTH(A2:A7)=6) will return all dates that occur in June, regardless of the exact day or year.

Why is my FILTER function returning a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds no matches for your criteria. Ensure your target date string correctly matches the "ddmm" format, or add an [if_empty] argument to your formula, like =FILTER(A2:A7,TEXT(A2:A7,"ddmm")="0106", "No matches found").

Does the FILTER function work in older versions of spreadsheet software?

The FILTER function is a modern dynamic array function. It is available in Microsoft 365, Excel 2021, and recent versions of WPS Office. If you are using an older version, you will need to use the helper column method or Pivot Tables.

Excel & Data Problems