Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Grouping Date By Month
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. |
#2
|
|||
|
|||
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. "Andy Graham" wrote: 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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
It says can not group that selection. any thoughts? -- rmm30 ------------------------------------------------------------------------ rmm30's Profile: http://www.excelforum.com/member.php...fo&userid=8358 View this thread: http://www.excelforum.com/showthread...hreadid=390130 |
#5
|
|||
|
|||
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. -- Regards Roger Govier "rmm30" wrote in message ... It says can not group that selection. any thoughts? -- rmm30 ------------------------------------------------------------------------ rmm30's Profile: http://www.excelforum.com/member.php...fo&userid=8358 View this thread: http://www.excelforum.com/showthread...hreadid=390130 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Round date to first of month | Excel Discussion (Misc queries) | |||
Lookup the month in a date string 01/03/05 | Excel Worksheet Functions | |||
Date Format Pivot Grouping | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions |