How to group dates into month?

[ThePeoplesClub]

Hey ppl!

I am having trouble with having a good display for my line graph to show difference over time, the x-axis is over exhausted with a number of dates...is there a way I can narrow the dates into month form on the x-axis without taking away the accurate date by date data.

Here is an attached example of my problem if anyone would like to look at it.

[DonkeyOte]

on my version of your file at least the dates in A are not dates they are text values... I would first create a new range of date values but ensure I have all values dated as 1st of appropriate month.

First in E2:F13 setup a table of month in mmm format to month number, eg

E2: JAN
F2: 1
E3: FEB
F3: 2

etc...

Then in D1 enter "Date", in D2:
=DATE(RIGHT(A2,4),VLOOKUP(LEFT(A2,3),$E$2:$F$13,2,0),1)
copy down for all data in D

Then highlight B1:D96 and create a Pivot Chart
Set Date as Row field, Category 1 and 2 as Data Fields
Set chart type to line or whatever you want.

Does that do it for you ?

[simjambra]

I am an excel noob but I would transfer the date values to 2 columns, month in A and date into B, then subtotal and create a graph based on this.

Sheet attached. I have no doubt that there will be much better and easier ways but I thought I would suggest haha sorry

[ThePeoplesClub]

on my version of your file at least the dates in A are not dates they are text values... I would first create a new range of date values but ensure I have all values dated as 1st of appropriate month.

First in E2:F13 setup a table of month in mmm format to month number, eg

E2: JAN
F2: 1
E3: FEB
F3: 2

etc...

Then in D1 enter "Date", in D2:
=DATE(RIGHT(A2,4),VLOOKUP(LEFT(A2,3),$E$2:$F$13,2,0),1)
copy down for all data in D

Then highlight B1:D96 and create a Pivot Chart
Set Date as Row field, Category 1 and 2 as Data Fields
Set chart type to line or whatever you want.

Does that do it for you ?

The bit in bold when entered into D2 comes up within Cell D2: #NA

[DonkeyOte]

did you create the lookup table in E2:F13 ?

[ThePeoplesClub]

Yes, but I am suppose to be writing the dates in MMM form manually like for instance me writing JAN instead of January?

[DonkeyOte]

see attached - normally I like people to do it themselves but in this case we could be going to and fro for a bit given it's all a bit of a faff.

Note Pivot Table & Chart are on sheet 6.

[ThePeoplesClub]

That chart on sheet 1 is just what I wanted, except instead of having 1,5,9 on the x-axis is there a way I can replace these numbers for the month?

Example:

1 = January 2007
5 = Febuary 2007
..etc

[ThePeoplesClub]

I solved the problem late yesterday...I had redone all the dates to data form and not text form so that excel would recognise the dates and then gave me the option to change the scale in graph options.

Thank You for your help!