[Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions

The date grouping feature in Excel is a very powerful tool that will save you a lot of hassle. It helps you analyze the data in a large worksheet while grouping the data by dates, weeks, months, quarters, and years. The date grouping feature, however, doesn’t always work. Sometimes, you will see that the Group Field button on the Analyze/Options tab of the PivotTable Tools ribbon is disabled or grayed out. You might get an error, “Cannot group that selection”. In this tutorial, we will take a look at why we cannot group dates in pivot table and how we can solve the problem.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


4 Easy Methods to Solve Group Dates Problem in Pivot Table

Let’s assume we have an Excel large worksheet that contains the information about various fruits and vegetables that a country has imported to three different countries in Europe. The Excel file has the Product Name, Exported Amount, Importer Country, Order Date, and Ship Date. We will use the Order Date and Ship Date column of this Excel to solve the group dates problem in the pivot table. We will use Filter, Go To Special, and VBA Macro to solve the problem as to why we cannot group dates in pivot table. The image below shows we are getting the error “Cannot group that selection” while trying to group dates for this Excel worksheet.

Cannot Group Dates in Pivot Table

Note: If you do not know how to insert a pivot table in an Excel worksheet, please see this step-by-step guideline to learn how to insert a pivot table in Excel.

Method 1: Replace Missing or Distorted Date Values to Group Dates in Pivot Table

Step 1:

  • The only way to avoid this error while trying to use the Group Field feature for dates is that all cells in the date columns in the source data must contain dates or the cells could be blank. If there are any cells in the date field of the source data that contain text or errors, then the group feature will NOT work. For example, we have some error date values like the image below in our source data.

Replace Missing or Distorted Date Values in Pivot Table

  • In most cases, due to these faulty date values, we can not group dates in pivot tables in Excel.
  • To solve this problem, we have to insert dates on those cells that do not have a date value. If the cells have error date values like the image above, we have to correct the errors.
  • For this example, we have corrected the 3 error dates that we are seeing above.

Corrected 3 Error Dates in Excel

Step 2:

  • Now, to group dates, we will create a pivot table first. We will drag the cells like the image below to create the pivot table.

Drag the Cells Like the Image Below to Create a Pivot Table

  • Our pivot table will be like the image below.

Pivot Table Created by Excel

Step 3:

  • Now, we will select any cell containing a year. We will then will right-click on the cell. A window will appear. We will click on Group from that window.

Cannot Group Dates in Pivot Table

  • Another window titled Grouping will now appear. We can select how we want to group our data. We have selected Quarters and Years to group our data by.
  • Then, we will click on OK.

Select the Criteria to Group the Dates in Pivot Table

  • Now, we will see that our information has been grouped by Quarters and Years.

Pivot Table in Excel Grouped By Quarters and Years

Read more: How to Use Excel Pivot Table to Group Dates by Month and Year


Method 2: Filter to Find Out Distorted Date Values to Group Dates in Pivot Tables

We will now look into some ways to find out the distorted or error date values. There are 3 easy ways to find out such dates. The first one is using the filter to find the date values with errors. Follow these steps to use the filter to find the dates with errors.

Step 1:

  • Select all the cells in the data range. Then, click on the Filter option under the Data.

Select Filter Option Under the Data Tab

  • Now, click on the small downward arrow on the right side of the Ship Date You will see the filter drop-down menu has grouped all the date values in this column by Year, and Date. All text and error date values are listed at the bottom of the list.

Filter the Error Date Values

Step 2:

  • We can see that this column contains some dates that were entered in the incorrect format. Excel did not consider these as dates when they were entered in the cell and so it identified them as text.
  • To filter the Text and ERROR values, uncheck all the date items Or remove the tick beside the Select All option. Then select the text and error items.
  • Click OK.

Cannot Group Dates in Pivot Table

  • The column will now be filtered to only display the text and error values.

Filtered Dates that Contains Errors

Step 3:

  • The next step is to determine why the values are not in the correct date format and fix them. We have corrected all the filtered dates with errors. See the image below.

Corrected Filter Dates in Excel

  • Now, we will click on the Filter option again.

Click on the Filter Button Again

  • The data range will be returned to the initial form with the faulty dates being corrected this time.

Cannot Group Dates in Pivot Table

  • Now, we can group the dates as there is no distorted date in our data.

Read more: How to Group Pivot Table by Month in Excel


Similar Readings


Method 3: Find Out Error Date Values Using the GoTo Special Menu to Group Dates in Pivot Table

The GoTo Special menu in Excel is a wonderful feature that allows us to select cells containing different types of data such as constants, blanks, formulas, comments,  etc. We can also use it to select the cells that contain data of specific types.

Steps:

  • First, we will select the entire column of the date field. We can press the CTRL+SPACE together.
  • Go to Find & Select under the Home tab. Select Go To Special from the drop-down menu.

Select Go To Special Menu

  • We will Select the Constants radio button.
  • Then Uncheck the Numbers checkbox.
  • Click OK.

GO To Special Menu in Excel

  • All cells that contain text or error values will be selected. We can then apply a different fill color to highlight these cells so that we can fix them in the next step. For this example, we have selected Red as the fill color to flag the dates with error values.

Select Red Color to Highlight the Dates with Errors

Read more: Excel Pivot Table Group by Week


Method 4: Find Out Error Date Values With VBA to Group Date Values in Excel

We can also use VBA to determine the data type of a cell. There are several ways to do this. The easy way is to use the CellType function that will take a cell value as an argument and return the data type of that cell.

  • Click ALT+F11 to open the Visual Basic You can also open it from the Developer tab.

Select VBA Basic From Developer Tab in Excel

  • Click on the Insert button and select Module.

Select Error Date Values in Pivot Table With VBA

  • Write down the following code in the window that appears.
Public Function CellType(c)
    Application.Volatile
    Select Case True
        Case IsEmpty(c): CellType = "Blank"
        Case Application.IsText(c): CellType = "Text"
        Case Application.IsLogical(c): CellType = "Logical"
        Case Application.IsErr(c): CellType = "Error"
        Case IsDate(c): CellType = "Date"
        Case InStr(1, c.Text, ":") <> 0: CellType = "Time"
        Case InStr(1, c.Text, "%") <> 0: CellType = "Percentage"
        Case IsNumeric(c): CellType = "Value"
    End Select
End Function
  • Click on the File tab and save the Excel file.

Cannot Group Dates in Pivot Table

  • Now, we will go back to our source worksheet and write the following function like below in cell G5:
=CellType(F5)

Write the Function CellType in the Cell

  • Upon pressing ENTER, we will get the data type of cell F5. The function will return Date as data type.

CellType Function Shows the Data Type of the Cell

  • We will drag the fill handle downward to apply the function to the rest of the cells in the Ship Date We will find out that it is showing data type as Text for the dates with error values.

  • We will highlight the cell with data type as Text as we have done before. In the next step, we will correct those cells.
  • After we have corrected those cells, we can group dates as we have done in Method 1.

Things to Remember

  • You can also use the VBA CellType function to determine the type of other data in a cell.
  • Select New Worksheet when you are creating a pivot table. If you select Existing Worksheet, a pivot table will be created in your existing sheet that contains the data. There is a substantial risk of data being distorted if we create the pivot table in our existing worksheet.

Conclusion

In this article, we have learned how to solve the problem when we cannot group dates in a pivot table in Excel. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo