Grouping Date By Month

[Guest]

I have a list of transactions all with different dates and I want to display
a pivot chart with a month by month summary. Trouble is I cannot get my data
across the top to display anything but dd/mm/yy format. How can I get it to
display just in a mm/yy format?
I tried to reformat the orignal data using the CUSTOM cell format but that
only changes the way the data is displayed as opposed to the data itself.

 

[Guest]

I would suggest you create another column and use the EOMONTH function to set
the a date as the last day of the month ( to see how to use the EOMONTH
function you can look at http://www.auditexcel.co.za/date.html for a video
clip). Now when you use the PIVOT table you will be able to pivot on the
actual date or a common end of month date. Hope this is what you were looking
for.

 

[Phil]

Hi Andy

Once you've got the dates into the pivot table, right-click on the Date
field's header, choose Group and Show Detail from the menu, then Group.
You should see a dialogue box that allows you to group the dates into
months, years or quarters - or all of them!
HTH

Phil

 

[rmm30]

It says can not group that selection. any thoughts?

 

[Roger Govier]

Check that there are true date values in the complete column of data that is
being pivoted.
PT doesn't group if cells are blank.