Excel – Grouping dates by week in a Pivot Table

datemicrosoft-excel-2007pivot table

I am trying to create a Pivot table to report on data that comes from an Access Query. Basically the data that comes back is in the following format:

TradeDate     StoreID     DeptCode     ReasonCode     CostEx
26-Jul-2010     1            1             1           9.99

etc etc.

The problem is that all of the 'TradeDate' figures are in days, and I need to report in weeks. I have a Pivot Table set up in Excel 2007 that reports on the data fine, except it reports in days. I have looked around but cannot find an answer that lets group to the week ending date (sunday for me).

So what I need is something to say that everything between 26-Jul-2010 and 1-Aug-2010 (in this case) gets summed and reported just under the 1-Aug-2010 title.

There are other criteria, the figures have to be from the same StoreID, DeptCode and ReasonCode, but I'm not having any troubles making that part work so far.

Is it possible to get a Pivot Table to do this?

Best Answer

  1. Right click the Date field button.
  2. Choose Group and Show Detail | Group
  3. In the Grouping dialog box, select Days from the 'By' list.
  4. For 'Number of days', select 7
  5. The week range is determined by the date in the 'Starting at' box, so adjust this if necessary. In the example below, December 29, 2003 (a Monday), was entered as the starting date.
  6. Click OK

alt text

Not sure if that was what you were looking for, but I hope it helps. Source.

Related Question