How To Create A Pivot Table In Excel With Dates

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

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.

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:

Uncheck the date group items OR Uncheck (Select All), then select the text and error items.
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.

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

Select the entire column of the date field [keyboard shortcut: Ctrl+Space Bar].
Open the GoTo Special menu (Home tab > Find & Select menu > GoTo Special…) [keyboard shortcut: F5, Alt+S]
Select the Constants radio button option.
Uncheck the Numbers checkbox.
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 theTypeName 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 rangeFor            Each            c            In            Selection.Cells'Determine if the cell contains a dateIf            TypeName(c.Value) <> "Date"            Then'Add the cell address to the Immediate Window            Debug.Print c.AddressEnd            IfNext            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.