Sorting Year & Month in Pivot table

[SHAJEE]

how to sort Year and Month in Pivot table?
the formula i applied on the date field on database for consolidating Year & Month is
=YEAR(B2)&(CHOOSE(MONTH(B2)," JAN"," FEB"," MAR"," APR"," MAY"," JUN"," JUL"," AUG"," SEP"," OCT"," NOV"," DEC")
kindly find the result , whcih is not in an order.
Iam expecting an ascending order result, please help.........

Row Labels

2011 APR</SPAN>

2011 DEC</SPAN>

2011 MAY</SPAN>

2011 NOV</SPAN>

2011 OCT</SPAN>

2011 SEP</SPAN>

2012 APR</SPAN>

2012 DEC</SPAN>

2012 FEB</SPAN>

2012 JAN

</SPAN>

<TBODY>
</TBODY>

 

[oldbrewer]

make a custom sort list starting with 2011 APR ending with 2012 DEC

 

[SHAJEE]

thanks, but custom sort only takes 255 ch , hence this will not SUPPORT even three year (ie from2011 jan to 2013 dec )
kindly suggest any other option ............

 

[oldbrewer]

2011 APR	1	********
2011 DEC	6
2011 MAY	2		>>>>>>>	2011 APR	1
2011 NOV	5			2011 MAY	2		this table has all the dates
2011 OCT	4			2011 SEP	3		in calendar order, ranked
2011 SEP	3			2011 OCT	4		1 to 10
2012 APR	9			2011 NOV	5
2012 DEC	10			2011 DEC	6		cell marked >>>>>>>
2012 FEB	8			2012 JAN	7		is E4
2012 JAN	7			2012 FEB	8
				2012 APR	9
				2012 DEC	10
the table above is your dates
in your order, the second
column finds the ranking number
from the table to the right
				1	2011 APR	#######
				2	2011 MAY
				3	2011 SEP		this table FINDS the ranking number
				4	2011 OCT		in the first column IN
				5	2011 NOV	the second column of top left table
				6	2011 DEC		and brings back the date
				7	2012 JAN		to the left of it
				8	2012 FEB
				9	2012 APR
				10	2012 DEC
cell marked ******* (B2)
=OFFSET($E$3,MATCH(A2,$E$4:$E$13,0),1)
cell marked ##### (F23)
=OFFSET($B$1,MATCH(E23,$B$2:$B$11,0),-1)
this approach will work for any number of dates

<colgroup><col><col span="10"></colgroup><tbody>
</tbody>

 

[oldbrewer]

ignore screwed formatting all years and months are same line same cell

 

[SHAJEE]

i failed to apply this,could you please elobrate liitle more how to apply this setpwise...

 

[oldbrewer]

PM me with your Email address and I will send the spreadsheet to you - it is difficult to explain when the formatting goes mad

 

[SHAJEE]

Thanks a lot, my email is [email]shajee.svk@gmail.com[/EMAIL]

 

[SHAJEE]

Done, by choseing date field custom format to YYY-MM, thanks for the help