Data Import & Export

How to Fix Incorrect Excel Dates and Filter by Month

Ayan Masood Oct 14, 2026 872 views

Question details

The user needs to correct improperly formatted or text-based dates in Excel so they can effectively group and filter records by month.

How to Fix Incorrect Excel Dates and Filter by Month

Product: Excel
Device & OS: not provided
Scenario: Filtering a dataset by month when date columns display unexpected formats or behave as text strings.
Observed behavior: Excel fails to group dates by month in the filter dropdown because the cell values are recognized as text or formatted incorrectly instead of true date values.

Before you start

Before making changes, verify if your dates are currently aligned to the left (often indicating a text format) or to the right (indicating a proper number or date format) in their cells.

Solution 1Recommended

Apply the Correct Date Format

Use the Format Cells menu to change the cell formatting from General or Text to a recognized Date format, allowing Excel to recognize the values correctly.

If your dates are actually stored as numbers but simply displayed incorrectly, changing the cell format is the fastest way to resolve the issue.

Select the cells

Highlight the column or specific cells containing the dates you want to fix.

Open Format Cells

Right-click the selected cells and choose 'Format Cells' from the context menu.

Choose Date format

Go to the 'Number' tab, select 'Date' under the Category list, choose your preferred date format, and click 'OK'.

Reapply filters

Click 'Data' > 'Filter' to refresh your filters and check if the dates now group correctly by month.

Filter Grouping: Once formatted correctly, Excel's filter dropdown will automatically group the dates hierarchically by Year, Month, and Day.

Solution 2

Convert Text Strings to Dates using Text to Columns

If changing the format doesn't work, the dates are likely stored as text. The Text to Columns wizard can convert them into real date values.

Solution 3

Extract True Dates with the DATEVALUE Function

Use a formula to convert text representations of dates into Excel serial numbers that can then be formatted properly.

Manage Data Efficiently

Fix and Filter Dates Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides intuitive tools to instantly recognize, format, and filter date values. Whether you're converting text to dates or grouping data by month, WPS Office handles it seamlessly while ensuring a familiar user experience.

1. Open your file: Launch WPS Spreadsheet and open the dataset containing the incorrect dates.
2. Apply formatting: Highlight the date column, right-click, and select 'Format Cells' to apply a standard Date format.
3. Enable filtering: Go to the 'Data' tab on the top ribbon and click the 'Filter' icon.
4. Filter by month: Click the filter dropdown on your date column header; WPS Spreadsheet will automatically group your true date values by month and year.

Easily format, sort, and group dates by month, quarter, or year.Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) file formats.Built-in Text to Columns and formula tools for quick data conversion.Free and lightweight alternative to heavy office suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my Excel dates not grouping by month in the filter dropdown?

This usually happens because Excel reads the data as text strings rather than actual date values, or because the cells are strictly formatted as 'General' or 'Text' instead of a standard 'Date' format.

How do I group by month in an Excel Pivot Table?

Once your dates are formatted as true date values, right-click any date in your Pivot Table, select 'Group', and choose 'Months' from the grouping dialog box to automatically aggregate your data.

Why does the DATEVALUE function return a #VALUE! error?

The #VALUE! error occurs if the text string is in a regional format that Excel does not recognize as a valid date. You may need to use string manipulation functions like LEFT, MID, or RIGHT to extract and rearrange the date parts before converting.

Excel & Data Problems