I’m trying to graph something that is grouped by month. To do this and can span more than one year. How would I group data so the days are grouped into months in a pivot chart.
I would like the first column converted into month and year (not just formatted)
1/1/2007 Jan-07
1/3/2007 Jan-07
3/2/2007 Mar-07
5/1/2007 May-07
5/5/2007 May-07
5/6/2007 May-07
I would like to pivot the above information so I get it in this form to graph:
Jan-07 2
Mar-07 1
May-07 3
I was able to do this by converting it to text =text(cell#,“mmm yyyy”) but the pivot table then treats it as text and sorts it alphabetically so I end up with Apr 2007, Apr 2008.
A good thought but it just sorts it by year first and then goes back to alphabetical sorting so May woud be after July. If I change it to “YYYY M” it then sorts month numbers 1, 11,12 first.
Oh, to clarify, I meant I always use yyyy-mm (numbers for month instead names).
I think one way to do it is to have 2 redundant columns. One that is formatted as numbers such as “yyyymm” (numbers) and another as “yyyy-mmm”. The “yyyymm” is the one you sort on, and you can “hide” it or make the width very narrow so not to see it. The other column “yyyy-mmm” is the one that’s visible on the report.
:smack: I can’t believe I didn’t see that. a 2 digit month works perfectly. Thanks. I was looking at all kinds of convoluted If statements. Talk about getting into a mental rut. That works perfectly.