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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube