If you’re working with dates in a pivot table, it’s easy to group them by years, months or days – those are options in the Group By dialog box.
However, sometimes you might need a different type of date grouping. In this example, we’d like to group the sales data into 4-week periods, to match our company’s sales calendar. Keep reading to see the steps to set that up, and make sure the period starts on the correct weekday.
Also, you can read more about pivot table grouping on my Contextures website.
Group Dates by Week
To group dates into 1-week periods, use 7 as the number of days. There are instructions on my Contextures website: Group Pivot Table Dates by Week
Group Dates by 4-Week Period
In each of our sales periods, there are 4 weeks. There’s no option for grouping by Weeks, but we could use the Days option instead. With a bit of simple arithmetic, we can calculate the number of days per period:
- 7 days per week
- 4 weeks per period
- = 28 Days per Period
Follow these steps to group the dates into 4 week periods:
- Right-click on of the dates, and click Group
- In the Grouping window, click on Days, to select it
- If any other grouping types are selected, click on them, to unselect them
- Set the number of days to 28
Using the Default Starting Date
As a starting date, Excel automatically selects the first date in your source data. In the the screen shot above, you can see that January 1, 2013 is the starting date in this source data.
If I check in Outlook, that date falls on a Tuesday.
If I click OK in the Grouping window, Excel will create periods that start on a Tuesday. In the grouped dates shown below, the first period goes from Tuesday, January 1, 2013, to Monday, January 28, 2013.
Fix the Starting Date
Instead of using the default starting date, you can enter a different date, to get the starting date you need.
In this example, we’ll change the starting date to December 31, 2012. That date falls on a Monday, so this forces all the groupings to start on a Monday, which matches the company’s sales periods.
To fix the starting date:
- Right-click on one of the grouped date cells in the pivot table
- Click Group
- In the Grouping window, change the starting date – December 31, 2012
- NOTE: The Auto check box for “Starting at” is automatically cleared – leave it unchecked.
- Click OK
Now the periods are grouped correctly, with a Monday as the starting date for each period.
Video: Group Dates by Week
Watch this video to see how to group the date field, and set the starting date.
Or watch on YouTube: Group by Weeks in Excel Pivot Table