Calculation Issues

Fix Excel Date Filters That Do Not Match Numeric Searches

Huda QurayshiHuda Qurayshi Nov 22, 2026 868 views

Question details

Find an Excel date using a numeric date when the filter search box does not match the displayed date label.

Fix Excel Date Filters That Do Not Match Numeric Searches
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.
Before you start

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.

Solution 1Recommended

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.

1
Turn on filtering

Select a cell in the date column, then choose Data > Filter if the filter arrows are not already visible.

2
Choose an exact date

Open the date column's filter arrow and select Date Filters > Equals.

3
Apply the condition

Enter the desired date in the dialog using a format Excel recognizes for your regional settings, then select OK.

Filter for an exact date with Excel Date Filters
If Date Filters is unavailable: Check whether the column contains dates stored as text. Actual Excel dates support date filtering, sorting, and date calculations.
Filter dates in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 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. 3. Enable the filter: Select a cell in the table and choose Data > Filter to add filter arrows to the headers.
  4. 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.
Filter a date column without re-entering actual dates as textDisplay dates in a short numeric formatOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

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.