Excel 2007 : Month/Year Format with Pivot Table - Excel 2007

Hi,

I'm trying to build an Excel tool for users to check material spend/costs throughout the year, based on data "pulled" from a central database. I've managed to create the query (using MSQuery) to get the raw data, and added a worksheet to do the various calculations I need. I'm struggling however to show the data in a Monthly format in a Pivot Table. The only date I can use is in dd/mm/yyyy hh:mm:ss format. I know how to customise the format to show it as Jan 2012 for example, but when I try and pivot the info it is still showing an individual record for each date, rather than grouping by the Month/Year.

Is it possible to do something like this with Pivot Tables? TBH, I'm not a huge fan of them so very rarely use them, I mainly use Access for reports/queries etc, but the users are more used to Excel. I would try and export the data from Access to Excel but we are all remote/home users and the network link isn't the greatest, so the fewer steps I can use the better.

Hi,

righ-click on a date in the pivot table, then select "Group". Then select Year and Month and click OK.

If that does not help, please post a sample file.

That's done the trick, thanks. Didn't realise what that function was for! Now all I have to worry about is making the Pivot Table dynamic - which I think I can do, but I'll be back if I can't!

Thanks again

Thanks for the feedback. You can make the data source for the pivot table dynamic. See here how: http://contextures.com/xlPivot01.html