Pivot Table - Grouping dates by weeks in Microsoft Excel

You will certainly want to create groups of seven days that begin with the first day of the week.
To do so, you must locate the first Sunday or Monday of the data table and define it as a date to begin grouping the dates.
Often, the location of the first Sunday will be before the first date of the data table. For example (see screen shot), the first date of the data table is 5/10/1996. To determine if this is a Sunday:

  1. In any cell outside the PivotTable, enter the date 5/10/96.
  2. Select the cell and press Ctrl+1 (Format Cells).
  3. Select the Number tab.
  4. Select Custom. In the Type box, type dddd.
  5. Click OK.

The result – Friday. The conclusion – the first Sunday before 5/10/96 is 5/5/96.

  1. Restore the PivotTable to its original structure, in which the fields are located in the upper left of the PivotTable (Page) and are not filtered. Notice the appearance of the word All in the Field Name box. The data area includes one total row for the data.
  2. Drag the date field and position it in Row.
  3. Select one of the dates in the Date field.
  4. Right-click, select Group and Outline from the shortcut menu, and select Grouping.
  5. In the Starting at box, type 5/5/96, and leave the check box empty. Select Days.
  6. For the number of days, type 7.
  7. Click OK. Screenshot // Pivot Table - Grouping dates by weeks in Microsoft Excel
    Pivot Table - Grouping dates by weeks in Microsoft Excel

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.