Fix Excel Pivot Table Dates That Will Not Group in WPS Office Spreadsheets
Question details
The user needs to convert imported date text into real Excel dates so a PivotTable can group dates by month, quarter, and year.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user imported data containing dates as text and wants to summarize those dates by period in a PivotTable.
- Observed behavior
- PivotTable date fields do not group or group incorrectly because the imported dates are stored as text instead of real dates.
Check the source date column by clicking a date cell and looking at the formula bar. Make sure the selected range includes all date rows that the PivotTable uses before converting.
Convert Imported Date Text to Real Dates with Text to Columns
Use this when imported dates are stored as text and the PivotTable will not group them; it converts the source date column in place and refreshes the existing PivotTable.
PivotTables only group fields that are recognized as dates. If dates are imported as text, the spreadsheet treats them as labels, so the Group Field option can be unavailable or the grouping can fail.
Open your worksheet, click the column letter that contains the imported dates, or select only the date cells you need to convert.
Go to the Data tab and click Text to Columns in the Data Tools group.
Select Delimited and click Next twice. In the Step 3 dialog, choose DMY or the order that matches your imported data from the Date column format list, then click Finish.
Click any cell in the PivotTable, go to the PivotTable Analyze tab, and click Refresh.
Right-click any date in the PivotTable, choose Group, select Months, Quarters, and Years, and click OK.
Fix PivotTable Date Grouping by Converting Text Dates in WPS Office
WPS Office Spreadsheets includes Text to Columns and PivotTable grouping, so you can convert imported date text into real dates and summarize them by month, quarter, and year without leaving the workbook.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the source date column and PivotTable.
- 2. Convert the date column: Select the source column, click Data > Text to Columns, click Next twice, set the date format to DMY or the matching order, and click Finish.
- 3. Refresh the PivotTable: Click inside the PivotTable, choose Refresh from the PivotTable Analyze tab, then right-click a date and select Group.
- 4. Choose grouping levels: Select Months, Quarters, and Years in the Grouping dialog and click OK to update the report.
Frequently Asked Questions
Why does my PivotTable say cannot group that selection for dates?
This usually means the dates are stored as text. Select the source date cells, go to Data > Text to Columns, convert them to Date by choosing the correct order, and then refresh the PivotTable.
How do I know if my imported dates are text?
Click a date cell and look at the formula bar. If the value has an apostrophe, is left-aligned, or does not show a date serial number, it is likely text rather than a real date.
Can I group PivotTable dates by month and year only?
Yes. Right-click any date in the PivotTable, choose Group, clear the unwanted levels, select Months and Years, and click OK.
What date order should I choose in Text to Columns if my data is MDY?
Choose the order that matches your source values, such as MDY for month/day/year data or DMY for day/month/year data, so the conversion is correct.
Do I need to recreate the PivotTable after converting text dates?
Usually no. Click inside the existing PivotTable, go to PivotTable Analyze > Refresh, and it will recognize the converted dates.
