Excel: Fix Pivot Table Grouping for Combined Dates and Times by Week and Hour
Question details
The user needs to group combined date-and-time values in an Excel pivot table by week and hour.
- Product
- Microsoft Excel pivot tables
- Device & OS
- not provided
- Scenario
- The source data contains date-time values, but the pivot table must summarize them by seven-day periods and hourly intervals.
- Observed behavior
- Excel reports that the selection cannot be grouped, commonly because the source field contains text dates, blanks, errors, or other non-date values.
Inspect the complete source column used by the pivot table, including rows outside the current visible range. Look for blanks, error values, text dates, and mixed data types before refreshing or grouping the pivot table.
Clean the date-time source field and group it by week and hour
Use this solution when Excel says the pivot table selection cannot be grouped or the Group command is unavailable.
Pivot tables can group only valid date-time values in the entire source field. A single blank, error, text value, or other non-date entry can prevent grouping for the whole field.
In Excel, choosing Days with a value of 7 creates seven-day intervals. These intervals may not match calendar weeks that always begin on a particular weekday; use a helper column when fixed calendar-week boundaries are required.
Select the date-time column in the source table and use Home > Find & Select > Go To Special to review blanks and formulas returning errors. Also check for entries aligned as text rather than date-time values.
Select text date-time cells, choose Data > Text to Columns, select Delimited, click Next twice, choose Date with the correct order such as MDY, and click Finish. Alternatively, use a helper formula such as =VALUE(A2), fill it down, then copy and paste the results as values.
Replace or remove blank, error, and non-date entries in the source field. If the source is an Excel Table, extend the table range so every cleaned record is included.
Click anywhere in the pivot table, select PivotTable Analyze > Refresh, and confirm that the cleaned date-time field is available in the PivotTable Fields pane.
Right-click a date-time value in the pivot table, select Group, check Days and Hours, set Number of days to 7, and click OK. Expand the grouped fields to review weekly and hourly results.
Use helper columns when fixed calendar weeks are required
Use this option when the report must use consistent week start dates or when direct pivot grouping remains unreliable.
Group Clean Date-Time Data by Week and Hour in WPS Spreadsheet
WPS Spreadsheet can clean date-time source values, refresh a pivot table, and organize valid records into seven-day and hourly groups while preserving Microsoft Excel workbook compatibility.
- 1. Open the workbook: Launch WPS Office, open the Excel-compatible .xlsx file, and inspect the source date-time column for blanks, errors, or text values.
- 2. Normalize the date-time values: Select the affected cells and use Data > Text to Columns or a VALUE helper formula to convert text dates into real date-time values.
- 3. Refresh the pivot table: Click inside the pivot table and choose the refresh command from the PivotTable tools or right-click menu after correcting the source data.
- 4. Group the date-time field: Right-click a date-time item, choose Group, select Days and Hours, set the day interval to 7, and confirm the grouping.
Frequently Asked Questions
Why does Excel say that the selection cannot be grouped in a pivot table?
The source field usually contains at least one blank, error, text date, or other non-date value. Check the entire source range, convert text dates to real date-time values, correct invalid records, and refresh the pivot table.
How can I group a pivot table into weeks and hours?
Right-click a valid date-time value in the pivot table, select Group, check Days and Hours, enter 7 for Number of days, and click OK. This creates seven-day intervals and hourly groups.
How do I convert dates stored as text before grouping them?
Select the text date cells and choose Data > Text to Columns. Advance to the final step, select the correct Date format such as MDY, and click Finish. You can also use =VALUE(A2), then paste the results as values.
Will a seven-day pivot grouping always match calendar weeks?
No. Grouping by 7 days creates consecutive seven-day intervals based on the first date in the group. For weeks that always start on Monday or Sunday, create a week-start helper column and use it in the pivot table.
What should I do after fixing invalid date-time values?
Click inside the pivot table and choose PivotTable Analyze > Refresh in Excel, or use the equivalent refresh command in WPS Spreadsheet. Then open the Group dialog again and select the required intervals.
