PivotTable report - Grouping the Date Field by Days, Months, Quarters, and Years

To group the Date field by days, months, quarters, and years:

1. Drag the Date field from Page (the fields in the top left corner) to Row (to the left of the data area).
2. Select one of the dates in the Date field (cell A9 in the screenshot).
3. Right-click, select Group and Show Detail from the shortcut menu, and then select Group.
4. In the Grouping dialog box, select Days, Months, Quarters, and Years, then click OK. Three fields have been added to the PivotTable report: Years, Quarters, and Months.
5. Click and drag each of the three new fields from Row to Page

CAUTION!!!
If the message Cannot group that selection appears, drag the Date field back to Page, and check the date column in the source data for empty cells or for dates that are not in date format, fix them all and then select the PivotTable report, drag the Date field to Row, click Refresh and try grouping again.

Grouping the Date Field by Number of Days and by Days, Months, Quarters, and Years
You can group a Date field only once, either by Number of Days or by Days, Months, Quarters, and Years.

If you want to group the Date field by both Number of Days or by Days, Months, Quarters, and Years:

1. Copy and insert the Date column in the source data so that there are two Date columns.
2. Change the heading title of the new Date column.
3. Add the new field to the PivotTable report, and then group each one of the Date fields, one by Number of Days and the other by Days, Months, Quarters, and Years.
Screenshot // PivotTable report - Grouping the Date Field by Days, Months, Quarters, and Years
PivotTable report - Grouping the Date Field by Days, Months, Quarters, and Years

Comments

  1. I am trying to get pivot data to work by being able to add up Multiple totals from columns together. At same time if a total falls within the range but column names changed I want that info to be included as well. So if I want totals from say column C thru column Z. But then when new info is placed it ends up being column C thru column AC, I want the new ones as well. If it ends up being column C thru column P, it’s adding in grand total of totals which I don’t want. I just want all up to the column before grand totals. Hoping this makes sense.

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.