MONTHS

[dbpcar]

I want to build a pivot table report that shows months as the header. I cannot get the table to build on months, only individual days. What am I missing?
The table I want has year on the column, and months on the row, producing a summary by month and year. I tried splitting the date apart, but that wasn't a perfect solution. I have about 8000 lines of data, and I am trying to avoid any more handling of the data than I absolutely have to.
Thanks
dbpcar

 

[xlbo]

If you have a date field that is entered into the PT, you can use the group function on it

Put the date as row field and also column field with your values in the centre

Right click in the row field (on any of the dates) and choose "Group & Show detail" and then "Group"
From the grouping dialog box, choose "Months"
et voila
Do the same for the column but choose "Years"

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[dbpcar]

I knew there was someone smarter than me out there. Many thanks

 

[SkipVought]

BTW,

For the Group feature to work with Dates in PivotTables...

ALL date cells in that column must have a valid date entered.

If you have blank date values, Group will not work!


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

good "heads up" Skip
bear this in mind dbpcar

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

Since I personally don't like to use the Group feature on Dates (mainly because I almost always have blanks in my tables) ;-) I always add a column that contains the formula:

=If(J2="","",Text(J2,"mmmm&quot) (assuming that J2 contains the date or is blank).

Then I just add the MonthName Column to my PivotSeries. But the group feature is probably the best way for you to go, I just wanted to throw in another way you could go.

Good Luck!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!