Bottom line: Learn why the pivot table date group feature is disabled, grayed out, or does not work, and a few quick tips for finding the problem.

Skill level: Beginner

pivot-table-group-field-button-disabled-not-working-for-dates

The date grouping feature of pivot tables is a great tool that can save us a lot of time.  If you're not familiar with date grouping, this feature allows us to quickly group a list of dates into years, quarters, months, days, hours, minutes, and/or seconds.

However, the date grouping feature doesn't always work.  The Group Field button on the Analyze/Options tab of the PivotTable tools ribbon might be disabled or grayed out.  Let's take a look at why this happens.

The Simple Rule for Grouping Dates in Pivot Tables

The simple rule for the enabling the Group Field feature for dates is:

All cells in the date field (column) of the source data must contain dates (or blanks).  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.

all-cells-must-contain-date-values-in-pivot-table-source-data-date-columns

I explain how to create date groupings in video #3 of my 3-part video series on pivot tables and dashboards.

Turn Data into a Dashboard with Pivot Tables

One of the most common questions I get from that video is that the date grouping feature is not working.  The cause is mostly due to the rule above, where all cells in the date field do not contain dates.

So, we are now on the hunt for TEXT and ERRORS! 🙂

Note: In Excel 2016, date fields are automatically grouped when added to the rows or columns area of a pivot table.  If you don't like the automatic grouping or are on a different fiscal calendar, then checkout my article on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data.

3 Ways to Find Text or Errors in a Column

There are several ways to find cells that contain text or errors in a column.  Here are a few quick tips to locate the bad eggs in our date columns…

The Filter Drop-down Menu Groups Dates

One quick way to determine if there are text or error values in your column is to use the checkbox list in the filter drop-down menu.

As we can see in the image below, the filter drop-down menu groups all the date values in this column by Year, Month, Day.  All text and error values are listed at the bottom of the listbox.

text-and-error-values-are-listed-below-date-groups-in-the-filter-drop-down-menu

We can see that this column contains some dates that were entered in the incorrect format.  Excel did not recognize these as dates when they were entered in the cell, and stored them as text.

To filter for the Text  and ERROR values:

  1. Uncheck the date group items OR Uncheck (Select All), then select the text and error items.
  2. Press OK.

The column will now be filtered to only display the text and error values.  The next step is to determine why the values are not dates, and fix them.

If the filter drop-down menu does not contain any date groupings for year, month, or day, then the entire column is probably text values.

Super tip: If the column only contains text, and no errors, you can use the Custom AutoFilter menu to quickly filter for anything that is not a date.  In the filter drop-down menu select Date Filters > Equals > type an asterisk * in the criteria box > press OK.

filter-for-asterisk-to-view-all-text-items-in-date-column

This will apply a filter for all the text values in the column.  Note that the technique only works for text, not logical (TRUE/FALSE) or error values.

Select Text and Error Cells with the GoTo Special Menu

The GoTo Special menu allows us to select different types of cells (cells with comments, constants, formulas, blanks, etc.).  We can also use it to select cells with specific data types.

goto-special-menu-to-select-text-error-and-logical-values

In this case, we can use GoTo Special to select cells with Text or Errors.  Here are the instructions:

  1. Select the entire column of the date field [keyboard shortcut: Ctrl+Space Bar].
  2. Open the GoTo Special menu (Home tab > Find & Select menu > GoTo Special…) [keyboard shortcut: F5, Alt+S]
  3. Select the Constants radio button option.
  4. Uncheck the Numbers checkbox.
  5. Press OK.

All cells that contain text, logical (TRUE/FALSE), or error values will be selected.  We can then apply a different fill color to flag these cells for fixing.

Find Date Cells with a Macro (VBA)

We can also use VBA to determine the data type of a cell.  There are several ways to do this.  One quick way is to use the TypeName function.

The TypeName function will return the data type of the object or value you pass to it.  We can put the following line of code in the Immediate Window and hit enter to get the data type of a cell.

<pre>?TypeName (ActiveCell.Value)</pre>

The string value “Date” will be returned if the cell contains a date value.

vba-typename-function-returns-the-object-or-data-type

Note: All cells in the column should be formatted as dates before running this code.

Checkout my article on 5 Ways to use the VBA Immediate Window for more on this technique.

Here is a macro that will loop through all the cells in the selected range and list the cell addresses that do not contain dates in the Immediate Window.  You can copy/paste the code to your own file.

Sub Check_Cell_DataType()
'Create a list of all cells in the selection
'that are not dates.  Cells must be formatted
'as dates first.

Dim c As Range

    'Loop through all cells in the selected range
    For Each c In Selection.Cells
        'Determine if the cell contains a date
        If TypeName(c.Value) <> "Date" Then
            'Add the cell address to the Immediate Window
            Debug.Print c.Address
        End If
    Next c

End Sub

The Debug.Print method prints the value to the Immediate Window on a new line.  Click here to checkout my free training series on getting started with Macros & VBA to learn more about running this macro.

Fixing the Dates Stored as Text

After we find the trouble makers, we have to fix the text values and convert them to dates.  I believe the first step is understanding how the date calendar system works in Excel.  Dates are stored as numbers and formatted to display a date in a cell.  That knowledge alone will get you along ways in fixing dates.

There are a TON of additional techniques for fixing bad date values.  Probably enough to write a book on.  Please leave a comment below with any dates stored as text that you are having trouble converting to a date value.  I will do a followup post with some common techniques.

21 comments

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

  • I have a data set containing about 30.000 records with a date column. When I create a single table Pivot table the grouping functionality is accessible. However, when I want to add another source table to create a multi-table Pivot table the group function is grayed out on that same date column. So I would argue that this column contains any errors. But perhaps I’m overlooking some stuff. Thanks for your help.

  • I have a date and hourly increments that I can’t get to group. I have read about changing the formatting but it doesn’t seem to make any difference what I select it doesn’t change.
    Meter# Time Register Read Gals of water
    63296371 01/01/19 00:00:00 6775550 130
    63296371 01/01/19 01:00:00 6775680 120
    63296371 01/01/19 02:00:00 6775800 120
    63296371 01/01/19 03:00:00 6775920 120
    63296371 01/01/19 04:00:00 6776040 130
    63296371 01/01/19 05:00:00 6776170 120
    Thank you for your help.
    Sincerely Scott Selee

  • Hi Jon,

    I’ve an issue with Pivot regarding grouping with dates. I want dates in Columns field. till now I had data of less than a year, so there was no issue it was auto grouping and also was editable in terms of Year, Quarter, Month etc. Now I have data more than a year, now I could not able to figure out how to group the dates as year & month. Do you have any solution?

    Thanks!
    Jaydeep

  • Hi. I was having an issue where the date was taken by the system and stored as a different time format. One is formatted to 3/3/2017 1:07:09 AM and the other one is 03/17/2017 21:48:29. I tried to group it under Pivot table but having an error “Cannot Group that selection”. Please help!

  • Thank you for this page. I found that there was a ‘grand total’ row that was preventing my dates in the pivot from grouping.

    Thank you,
    MW

  • Thanks a lot Jon, this is a very good article that will save me lots of time.

    I like how you integrate the immidiate window into your analysis. I thought this is VBA only.

  • Hi Jon
    Ive been fol lowing your 3rd Video on pivot tables but can’t seem to carry out the task where you are grouping revenues so you can plot a histogram.

    Im using excel for Mac and the group field function only seems to be available for dates and so Is greyed out ?

    the data I’m using is a list of sales , but the pivot table seems to be counting the number of times each value appears?

    Any idea ?

    Thanks
    Naz

    • Hi Naz,
      Each cell in the column will need to contain numbers. There cannot be any cells that contain text or blanks in that column. That is the most typical cause of the group button being disabled. I hope that helps.

  • Hi Jon-
    I am trying to group dates by month and I have read many blogs and ensured my data is correct per those blogs. But I still get the error message “Cannot group that selection”. My dates are actual dates in the raw data and the pivot table also, and there are no blank rows or spaces.

    I have tried everything. Do you have any idea why this continues to happen?

    Thanks.
    Priscilla

    • Hi Priscilla,
      It would be hard for me to determine this without seeing the data. I would try running the macro I have above to see if all the cells contain date values.

    • I had a similar problem and i’m still working on a complete solution. However, if your table is loaded to the Data Model then you cannot group in the pivot table, you can however group in Power Query.

  • I often have dates that looks like dates but do not behave like dates (e.g. exported from another system and are treated as text in Excel). I can fix these one at a time by pressing F2 (the edit button) and hitting enter and they convert to proper dates. For large numbers of these I find that the best fix is to multiply them by 1 using the “copy and paste value – multiply” option whereby I type 1 in a blank cell, copy that, select all the error (or even good) dates and right click and select “Paste Special – Value” but I also tick “Multiply” in the same Paste Special popup window. The result is that each cell is multiplied by the 1 that I copied.

    It is not really a sophisticated fix but it is simple and quick.

  • It’s not so bad when the data is all in Excel, it’s when it comes from a database or PowerQuery. Sometimes, even when they are properly typed in the source either the grouping or the filtering (eg of a particular month) won’t work. I usually have to create year/month columns. Other times it works perfectly.

    • Yes, it can be tricky to get everything stored as a date value in Excel. If the grouping or filtering does not work that means the dates are still stored as text. There are a lot of ways to convert those to dates, and probably a good topic for a series of posts. Thanks

      • I’ve found that for PivotTables using the DataModel, even if you set the datatype to Date or DateTime for a Date column, you can’t group by a set number of days because that option is greyed out, as is the Starting At and Ending At options. Grrr.

        • Hi Jeff,
          I agree that using data from a DataModel seems to cause issues where the option is greyed out. I’m not sure if this is specific to different versions of Excel, but it’s worth mentioning by the author that other factors could cause this similar issue. Thanks again for sharing.

  • Jon
    Good afternoon
    I have spreadsheet with data that the date of the repair is like below.
    The data is for about a whole year of information . Is there w way that I can just get allof the the June together and so on without doing them individual.
    I did try the above but it is not working for me.

    0000000000 1.4 65.31 06-Nov-14
    TINTA BRANCA 1 21.22 17-Nov-14
    PANEL ASSEMBLY HOOD 1 601.48 26-May-15
    0000000000 8 231.51 29-Jun-15
    SIKAFLEX 1 10.4 06-Aug-14

    • Hi Elba,
      Are the dates in their own column? If so, Excel should automatically recognize them as dates.

      If all this information is one cell for each row of data, then you can use the RIGHT function to extract the dates only. Or you can use the Text-to-Columns function and separate by space.

      I hope that helps.

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly