» Grouping the Date Field by Number of Days in Excel 2007

To Group the Date field by number of days:

1. Drag the Date field to Row Labels area in PivotTable Field List dialog box (this area must contain a Date field only).
2. Select a cell in the Date field, right-click and select Group.
3. In the Grouping dialog box, select Days, and in Number of days set the desired Number of days.



CAUTION!!! If the message Cannot Group that selection appears, drag the Date field out from Row Labels, go to the source data, check the date column in the source data for empty cells or for dates that are not in a date format, fix their format, then select the PivotTable report, refresh it, drag the Date field to Row Labels again and Group the field again.

To set the Grouping starting date as the first work day of the week:

Assuming you want to Group the dates by N number of days when the first work day is Monday.
1. Enter the first date in Date column in the source data, select the cell, and then press Ctrl+1 (to open Format Cells dialog box) -> Number tab -> Custom -> In Type box , type DDDD, to find which day of the week is the date that appears in the cell, then find the date of the first Monday before that date.
2. Select the PivotTable sheet and select a cell in the Date field, right-click and select Group.
3. In the Grouping dialog box, type the date you found in step 1 , then select Days, and in Number of days box set the desired days number.



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, as explained later.
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 separately, one by Number of Days and the other by Days, Months, Quarters, and Years.