Grouping Dates in PivotTables
I have a client that is tracking sales. We have over 100 individual records, and the data source is going to grow through out the year. With multiple sales each day, we’ll want to be able to sum total sales at each week. PivotTables are the obvious answer.
We need to group by calendar week, Mon-Sun.
First step after creating the Pivot Table was to Group the date field, based on 7 days:
Which resulted in this:
However, by default Excel is using the first date in the data source as the first day of the week (1/2/2012 was a Thursday, so the 7 day week is Thu – Wed) rather than Mon – Sun.
To fix this, we needed to edit the group settings so the start date was actually the Monday prior to the first record in our data source (12/30/2013):
(also, notice that the Auto Starting at and Ending at check boxes are unchecked. I entered 12/31/2014 as the end date while I was at it, since the data source will ultimately extend to the end of the year.)
and have the Pivot Table group look like this:
And that fixed that.