Group Months In Pivot Table

[mrhartley]

Hello, I have a pivot table as follows:

Book2
	B	C	D	E
9	Year	2005
10	Origin Country	United Kingdom
11	Freight Mode	AIR
12
13	Sum of Shipment Count
14	Month	Total
15	1	333
16	2	376
17	3	409
18	4	454
19	5	414
20	6	402
21	7	357
22	8	313
23	9	410
Sheet1

My source data has the months numbered as you can see in the PT
Of course month 1 = Jan, month 2 = Feb and so on.

I am trying to avoid changing my source data, I will if I have to but I would prefer not to.

Anyone know of any funtion the PT can handle that might interpret the numbers into text months?
So to replace 1, 2, 3 with Jan, Feb, Mar and so on.

Thanks everyone in advance.
Regards
Mark

 

[Norie]

Mark

How is the month/date formatted in the source data?

 

[Greg Truby]

I don't [know of a way to get the PT to do that]. Excel won't let me stick a calculated field in there as a row item. Rather than monkeying with the actual data, personally I'd just toss in a column with =text(date(yearcell,monthcell,1),"mmm")) and use that as the row source...

 

[mrhartley]

Thanks for the replies. Here is a tiny snippet of my source data, many rows and columns but it will show that the date is not formatted as such, it already is as a month number.

Customer Standard Reports.xls
	A	B	C	D	E	F	G
1	Shipment URN	Year	Month	Origin Country Code	Origin City Code	Destination Country Code	Destination City Code
2	7503914989	2005	1	GB	LON	AE	DXB
3	7503914792	2005	4	GB	LON	BH	BAH
4	7503923478	2005	1	GB	LON	AE	DXB
5	7503924625	2005	8	GB	LON	US	LAS
6	7503924553	2005	9	GB	LON	IT	NAP
7	7503924063	2005	9	GB	LON	SG	SIN
8	7503925789	2005	3	GB	LON	AN	CUR
9	7503923535	2005	2	GB	LON	SG	SIN
10	7503925831	2005	1	GB	LON	DE	DUS
11	7503921671	2005	2	GB	LON	BN	BWN
12	7503925432	2005	10	GB	LON	IT	FLR
Report Data

Like I say, I can go with Greg's idea, I really want to try and avoid another column, everything in my worksheet is heavily macro driven and so whatever I place in the source data by way of another forumla will need to be another macro etc.

I was just hoping for a 'clean' pivot table way or something that I was unaware of.

Thanks Greg, Norie
Regards
Mark

 

[Norie]

Mark

Is that how you receive the data?

By the way Greg's idea could easily be done with a macro.

 

[mrhartley]

yup, thats exactly how I get my data from another system.

I could in theory replace the column with a dates but that in practise will more likely be more difficult than implementing Greg's idea as that month column is already heavily used in pivot table and pivot charts.

I'll go with the extra formula and macro.

thanks for the input guys
Mark

 

[Greg Truby]

Yeah, well (cough, cough) about that suggestion I made... I would not use the formula =text(date(yearcell,monthcell,1),"mmm")) but rather =date(yearcell,monthcell,1) and then format the pivot table field as "mmm". Feed it in with the text() formula and you can't sort worth a lick as it will sort the months alphabetically. Feed it in as a date and it sorts properly. Sorry about the earlier suggestion, I knew better than that!