Fix Excel Date Filters That Do Not Match Numeric Searches
Question details
Find an Excel date using a numeric date when the filter search box does not match the displayed date label.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user enters dates numerically but displays them in a long-date format and wants to filter for a particular day.
- Observed behavior
- The filter search box does not recognize the numeric date as expected when the displayed or grouped filter labels use a different format.
Check that the column contains actual Excel dates rather than dates stored as text. Note the date format and regional settings used for numeric entries, since they determine how dates such as 09/20/2017 are interpreted.
Filter for an exact date with Excel Date Filters
Use an exact-date condition instead of relying on the filter search box to match a numeric date to a long-date label.
Select a cell in the date column, then choose Data > Filter if the filter arrows are not already visible.
Open the date column's filter arrow and select Date Filters > Equals.
Enter the desired date in the dialog using a format Excel recognizes for your regional settings, then select OK.

Change the date display format for filter searches
Use a numeric display format if you want filter labels to more closely match the dates you type.
Find and filter dates in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, display actual dates in a numeric format, and use the column filter to select the day you need.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Format the dates: Select the date cells, open Format Cells from the right-click menu, and choose a short-date format on the Number tab.
- 3. Enable the filter: Select a cell in the table and choose Data > Filter to add filter arrows to the headers.
- 4. Select the date: Open the date column's filter arrow and search for or select the displayed date. Apply the selection to show matching rows.

Frequently Asked Questions
Why can Excel accept a numeric date but not find it in the filter search box?
Excel interprets a valid date entry as a serial number, but the filter search commonly matches displayed or grouped labels. A numeric entry may therefore not match a long-date label.
Can I filter for a date without expanding the year and month?
Yes. Open the date column's filter arrow, choose Date Filters > Equals, enter the date, and select OK.
Will changing the date format change the stored date?
No. For an actual Excel date, Format Cells changes its appearance, not its stored date value. Formatting alone does not convert dates stored as text.
Can I display a date as September-20-17 and still filter by the numeric date?
Yes. Keep the cells as actual dates and apply a long-date or custom display format. Then use the filter arrow's Date Filters > Equals option to enter the numeric date.




