Pivot Table Issues

Fix Pivot Table Displaying Wrong Month in Date Header

Maira Mehtab Sep 22, 2026 871 views

Question details

The user is experiencing an issue where a project analysis pivot table shows month headers that do not match the actual underlying dates in the source data.

Product: Spreadsheet Software / Dynamics 365
Device & OS: not provided
Scenario: Analyzing project dates using a pivot table and attempting to group the data by month.
Observed behavior: The generated pivot table date headers display the wrong months, misaligning with the source date values.

Before you start

Verify that the date column in your source data contains valid, uniform date values rather than text strings, as text formats can cause pivot tables to group or read dates incorrectly.

Solution 1Recommended

Correct Excel Pivot Table Date Grouping and Formatting

Ensure the underlying data is correctly formatted as dates, check system regional settings, and refresh the pivot table grouping.

Standard spreadsheet pivot tables rely on exact date values to group columns into months. If days and months are swapped due to regional settings, or if some dates are formatted as text, the pivot table will generate incorrect month headers.

Format source data as Dates

Navigate to your raw data sheet, highlight the entire date column, right-click and select 'Format Cells'. Choose 'Date' and ensure the format matches your intended output.

Verify system regional settings

Check your operating system's regional settings (e.g., Windows Control Panel > Region) to ensure the system's Short Date format (like DD/MM/YYYY) matches how the raw data was entered.

Regroup the pivot table dates

Go to your pivot table, right-click on one of the date headers, and select 'Group'. In the Grouping dialog box, make sure 'Months' is selected, and click 'OK'.

Refresh the Pivot Table

Click anywhere inside the pivot table, navigate to the 'PivotTable Analyze' (or 'Options') tab on the ribbon, and click 'Refresh' to update the data and headers.

Solution 2

Troubleshoot Dynamics 365 View Discrepancies

If the issue originates from a Dynamics 365 report rather than a standard spreadsheet pivot table, address the specific D365 environment settings.

Efficient Data Analysis with WPS Office

Accurately Group Dates in Pivot Tables Using WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis tools, including seamless pivot table generation. With smart date recognition, you can effortlessly group project dates by month, quarter, or year without formatting errors.

1. Open data in WPS: Launch WPS Spreadsheet and open your project data file containing the dates.
2. Insert a Pivot Table: Highlight your data range, go to the 'Insert' tab, and click 'PivotTable'.
3. Add Date fields: In the PivotTable Fields pane, drag your date field into the 'Rows' or 'Columns' area.
4. Group by Month: Right-click any date in the generated pivot table, select 'Group', and choose 'Months' from the dialog box.
5. Refresh data: If you update the source dates, simply right-click the pivot table and click 'Refresh' to apply changes instantly.

100% compatible with Microsoft Excel (.xlsx) formats and pivot tablesSmart date recognition prevents common grouping and sorting errorsIntuitive right-click grouping makes month-by-month analysis effortlessFree, lightweight, and easy to use for all data reporting needs

microsoft office alternative - wps office

Frequently Asked Questions

Why does my pivot table refuse to group dates by month?

This usually occurs because the source data column contains blank cells, text formatted as dates, or invalid date values. To fix this, ensure every cell in your date column contains a valid, recognized date format and remove any text or blank entries.

How do regional settings affect pivot table dates?

If your system's regional settings use a DD/MM/YYYY format but your data is entered as MM/DD/YYYY, the spreadsheet may misinterpret the days as months (or vice versa). This will cause the pivot table to group the data under the wrong month.

Can I format the month names in the pivot table header?

Yes. Right-click the date field header in your pivot table, select 'Field Settings', and click on 'Number Format'. You can then choose a Custom format (like 'mmm' or 'mmmm') to change how the month names are displayed.

Excel & Data Problems