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:
- Create a pivot table with dates in the Row area. Select any date cell and choose Group Field from the Options dialog.
- 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.
- 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.
- The Number of Days is only available if you choose only Days.
Results: The report will be redrawn as a weekly report.
- 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.
Gotcha: After you group by weeks, Excel will not allow you to group by months, quarters, years, or any other selection.