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?