Error Message When Grouping Dates

In Excel 2007, you added a date field to the pivot table’s Row area, and you want to group the dates by year. However, you get an error message, “Cannot group that selection”, when you try to group the dates.

error - cannot group that selection

This error occurs if there are cells in the source data that contain text, instead of dates. For example, you may have entered text such as N/A, if a date was not available when the data was being entered.

Dates entered as text

How to Fix the Problem

To correct the problem, do the following:

  1. In rows contain text, such as N/A, delete the text, and leave the cell blank, or replace the text with a valid date.
  2. Refresh the pivot table, and try to group the dates.
  3. If you’re still unable to group the dates, remove the date field from the pivot table layout, refresh the pivot table, then add the date field to the pivot table again.

Note: In earlier versions of Excel, blank cells will also cause this error message. You can fill in actual dates, or use dummy dates, such as 2/2/2099, in the source data. Do not leave the cells blank if you want to group the dates.

More Information

For more information on pivot table grouping, please click here.

2 thoughts on “Error Message When Grouping Dates”

  1. Hi there,

    I have had the above problem with dates but it was due to my source data using a dynamic named range. If I create the Pivot Table via a fixed range, I do not get an error when grouping dates.

    I really would like to use a dynamic named range as my data increases.

    Anyone got any ideas that might help?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.