# Excel question on dates
**URL:** https://boards.straightdope.com/t/excel-question-on-dates/491317
**Category:** Factual Questions
**Created:** [March 30, 2009, 3:46pm UTC](https://boards.straightdope.com/t/excel-question-on-dates/491317 "2009-03-30T15:46:03Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Magiver](https://boards.straightdope.com/u/Magiver)
#### Post date: [March 30, 2009, 3:46pm UTC](https://boards.straightdope.com/t/excel-question-on-dates/491317/1 "2009-03-30T15:46:03Z")
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.
---
### Author:  [@Ruminator](https://boards.straightdope.com/u/Ruminator)
#### Post date: [March 30, 2009, 4:02pm UTC](https://boards.straightdope.com/t/excel-question-on-dates/491317/2 "2009-03-30T16:02:22Z")
> [@Magiver](#):
>
> 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.
---
### Author:  [@Magiver](https://boards.straightdope.com/u/Magiver)
#### Post date: [March 30, 2009, 4:52pm UTC](https://boards.straightdope.com/t/excel-question-on-dates/491317/3 "2009-03-30T16:52:48Z")
> [@Ruminator](#):
>
> 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.
---
### Author:  [@Ruminator](https://boards.straightdope.com/u/Ruminator)
#### Post date: [March 30, 2009, 4:57pm UTC](https://boards.straightdope.com/t/excel-question-on-dates/491317/4 "2009-03-30T16:57:57Z")
> [@Magiver](#):
>
> A good thought but it just sorts it by year first and then goes back to alphabetical sorting so May woud be after July.
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.
---
### Author:  [@Magiver](https://boards.straightdope.com/u/Magiver)
#### Post date: [March 30, 2009, 5:12pm UTC](https://boards.straightdope.com/t/excel-question-on-dates/491317/5 "2009-03-30T17:12:00Z")
> [@Ruminator](#):
>
> 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.🙂
sniff… I love the internet.