Excel question on dates

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.

It looks like you have 99% of the solution already.

Just reformat as “yyyy mmm” instead of “mmm yyyy”. I always work with dates in year-month-day format specifically for sorting and grouping purposes.

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.:slight_smile:

sniff… I love the internet.