MrExcel.com - Your One Stop for Excel Tips & Solutions

Group by Week in a Pivot Table

December 28, 2022 - by

Group by Week in a Pivot Table

Problem: The Grouping dialog allows grouping by second, minute, hour, day, month, quarter, and year. I need to group by week. How do I do it?

Strategy: In order to set up this grouping option correctly, you need to figure out the weekday where your data starts. The data set we’re using in this example has data for January 2, 2014. Use the Long Date format to see that this is Thursday. You will later make the report start at December 29, 2013 to that the weeks run from Monday through Sunday.

Now follow these steps:

  • 1. Create a pivot table with dates in the Row area. Select any date cell and choose Group Field from the Options dialog.

  • 2. In the Grouping dialog, Excel defaults to showing the entire range of dates of the data set. If you left the Starting At field unchanged, your weeks would all start on Thursday. Change the 1/2/2014 date to 12/29/2013 to have your weeks start on Monday.

  • 3. Unselect the Months selection by choosing it with the mouse. Select the Days choice. This will enable the Number of Days field at the bottom of the dialog. Use the spin button to move up to 7 days.

Although the Grouping dialog does not offer Weeks, if you select only the Days, then a spin button appears with Number of Days. Set this to 7. At the top of the dialog, edit the Starting At date so it starts on a Monday.
Figure 901. The Number of Days is only available if you choose only Days.

Results: The report will be redrawn as a weekly report.

The pivot table now shows weeks down column A. Each row says something like 12/29/2013-1/4/2014.
Figure 902. Excel will produce a report by week.

Additional Details: Excel does not add a “Week” field to the PivotTable Field List dialog. Instead, the field that formerly contained dates now contains weeks but is still called Date.

Additional Details: Some manufacturing companies use a 13-month calendar. You can group by 28 days to replicate this calendar. For bi-weekly payroll, group by 14 day periods.

Gotcha: After you group by weeks, Excel will not allow you to group by months, quarters, years, or any other selection.

This article is an excerpt from Power Excel With MrExcel

Title photo by Towfiqu barbhuiya on Unsplash