With time grouping, relationships across time-related fields are automatically detected and grouped together when you add rows of time fields to your PivotTables. STEP 3: In the Number of days section, type 7. I am trying to group the dates in the pivot table into months and years. 3)Then highlighted the date range 4)Then selected paste special and then selected ADD. I'm trying to count the events by month/year, but my pivot won't group the dates. Excel File: GitHub Repository I am unable to group fields in pivot table of Excel file.. Graffiti table is linked with Calendar table via Date (in PowerPivot > Data Model), so why is there difficulty in grouping the following. 5) Then highlighted the date range and made it short date using the quick menu. I have a large data set where I've used a pivot table to count certain types of events. Once grouped together, you can drag the group to your Pivot Table and start your analysis. Select any of the cells from the date column. Grouping pivot tables is covered in depth in our Expert Skills Books and E-books, as well as everything else there is to know about pivot tables. Right click on it and select group. In order to display the original dates without grouping, right click on the data, select Group. The Pivot table will group over the distinct values in the column in which case 1/1/2012 12:00:00 AM and 1/1/2012 1:00:00 AM would be treated as different values despite being from the same day. To group the dates by week, follow the steps below: STEP 1: Right-click on one of the dates and select Group. If you need to improve your knowledge of pivot tables and other advanced Excel features it could be of great value to you. Software: MS Excel 2016. My original date data is in mm/dd/yyyy format in a table that is refreshed through an embedded SQL script. Hereâs a pivot table in which I added â¦ Since that version, dates in Pivot tables have been automatically grouped too. Date Grouping in Pivot Tables. Select âMonthâ in the group by option and then click OK. In the dates Grouping options, you can redefine how you want to group the dates. I have formatted my dates in the workbook to the mm/dd/yyyy format which didn't work and I also tried converting in the data tab->text to columns->column data format=MDY and it still won't group. Group Dates in Pivot Table by Week. STEP 2: Select the day option from the list and deselect other options. The automatic grouping is a default setting that can be changed. In addition, some dates go to being SERIAL and some remain the same. Insert a Pivot Table, and then drag the date field to Rows,the dates are automatically grouped by Year, Quarter and Month by default. You will get a pop-up window to group dates. See my article on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data to learn more. I have included a picture on what happens. Pivot Table - Date - Group by Month does not work ... - When I select one date, I see "cannot group" - when I select all dates, it just group the dates * I restarted Excel * I verified that the group is date * I updated to the latest version of excel. Select a cell inside the pivot table in one of the date fields. 6) Re did the pivot table and got the same ERROR. Press the Ungroup button on the Analyze tab of the ribbon. Below are the steps you need to follow to group dates in a pivot table. Note: The time grouping feature is new in Excel 2016. This is how the group dates in Pivot Table by week will be displayed. View best response. You can create a column to sort the months and then create another column which returns the actual month names. Labels: Pivot Tables dates werenât grouped in earlier versions of Excel, but that behaviour changed in Excel 2016.