Grouping the Date Field by Number of Days

To group the Date field by number of days:

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 A8 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, and in Number of days, set the desired number.

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, click Refresh, drag the Date field to Row and try grouping again.

When displaying the Grouping dialog box, Starting at is the first date in the source data, which is 1/10/2002 in the above example.
To set the grouping starting dat
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 // Grouping the Date Field by Number of Days
Grouping the Date Field by Number of Days

Users are saying about us...

  1. Is there a way to group the dates by a particular day of the week. Example: If data contains daily sales, can we group total sales on mondays, tuesdays, wednesdays etc.?

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