Days to Months on a Pivot table

Hi all,

..I have a pivot table that picks up "dates" in the format of dd/mm/yy.

I would like the pivot table to report on these, but "summarised" into the respective month.

Is it possible to do this within the pivot tanble itself, or would I have to create another column in my table an manually input the relevant month?

Many thanks,

Stu

Hello,

you don't need another column. Right-click any date in the pivot table, then click "Group" in the context menu. In the ensuing dialog highlight Month AND Year (if your data spans more than 12 months!!) and hit OK. The pivot table will now group the data by year and month.

If you don't group by year, then January 2010 gets lumped in with January 2011 etc.

cheers,

You can Group the PivotTable, right click on a cell in the Dates Column & choose Group

This can be caused if you have empty cells in the column of data or the cells aren't formatted as dates

Stu,

if the column contains dates, or text that looks like dates, you can select the column, then click Data > Text to columns > Next > Next > tick Date and select the appropriate order for DMY or MDY for your location and click Finish.

That should ensure that all dates are real dates.

Then check the Pivot table source and make sure that it contains only the rows with data, and no additional blank rows.

cheers,

You can create a named range that serves a dynamic data source to the Pivot Table.

See here how it's done: http://www.contextures.com/xlpivot01.html

cheers,