Sort Pivot by Year

[murkaboris]

Hello:

I'm trying to sort pivot table by year but it doesn't seem to recognize it
as a date format. The results show YYYY for anything prior or after current
year and for current year it also includes quarter (i.e. 2009 Q1, 2009 Q2,
etc...). When I sort it puts the current year at the bottom but I need it to
be btw 2008 and 2010.
Any help would be appreciated.
Thank you.
Monika

Here is what the pivot looks like after I sort:
RSDD Qtr CT MR NUC RAD Grand Total
2014 1200 1200
2019 12000 12000
2009 Q1 1500 1500
2009 Q2 900 900
2009 Q3 1200 1200
2009 Q4 450 450
No Dates 120 120
39953 120 120
39984 240 240
40014 300 300
40045 1200 1200
40106 3000 3000
Grand Total 13440 5340 750 2700 22230

 

[murkaboris]

Sorry -- the pivot didn't display correctly, here it is how it shows after I
sort:
RSDD Qtr CT MR NUC RAD Grand Total
2005 120 120
2006 240 240
2007 300 300
2008 1200 1200
2010 3000 3000
2014 1200 1200
2019 12000 12000
2009 Q1 1500 1500
2009 Q2 900 900
2009 Q3 1200 1200
2009 Q4 450 450
No Dates 120 120
Grand Total 13440 5340 750 2700 22230

 

[Dave Peterson]

I think it's because you have a mixture of dates and text in that RSDD field.

I'd make sure that RSDD is all text (reformat and reenter the values that look
like dates/numbers). Or add a helper column:

=text(a2,"yyyy")
(or something like that)

 

[murkaboris]

Hi Dave:

Thank you for your response.
I've tried both options and didn't work. If I reformat the raw data to
"text" it sorts by year but puts 2009 at the end so it shows as:

2005
2006
2007
2009
2010
2011
2009 Q1
2009 Q2
etc...
What i really need is for the 2009 Q1 through Q4 to show in between 2008 and
2010.

If I use the formula below it changes all the years to 1905 outside of the
2009 Q1 thorugh Q4.

Please advise.
Thank you.
Monika

 

[Dave Peterson]

It's not enough to just change the format of the cell. You have to change the
format AND the value.

You can change the format to text and then select the cell, hit F2 and enter.

Or you can use that helper column filled with formulas.

 

[murkaboris]

Dave:

I've changed the format and the value per your instructions in the pivot but
when I sort it still puts the 2009 Q1 at the end not in the middle of the
years...so it displays as below, it doesn't move the 4 rows for 2009 in the
middle between the years 2008 and 2010.

Text RSDD CT MR NUC RAD Grand Total
2006 240 240
2007 300 300
2008 1200 1200
2010 3000 3000
2014 1200 1200
2019 12000 12000
2205 120 120
2009 Q1 1500 1500
2009 Q2 900 900
2009 Q3 1200 1200
2009 Q4 450 450
No Dates 120 120
Grand Total 13440 5340 750 2700 22230

 

[Dave Peterson]

Find one of the cells you fixed (say it's A222)

Then put:
=isnumber(a222)
in an empty cell on that same worksheet.

Do you see True or false?

If you see True, then you didn't convert the number to text correctly.

If you see False, I don't have an idea.

Dave:

I've changed the format and the value per your instructions in the pivot but
when I sort it still puts the 2009 Q1 at the end not in the middle of the
years...so it displays as below, it doesn't move the 4 rows for 2009 in the
middle between the years 2008 and 2010.

Text RSDD CT MR NUC RAD Grand Total
2006 240 240
2007 300 300
2008 1200 1200
2010 3000 3000
2014 1200 1200
2019 12000 12000
2205 120 120
2009 Q1 1500 1500
2009 Q2 900 900
2009 Q3 1200 1200
2009 Q4 450 450
No Dates 120 120
Grand Total 13440 5340 750 2700 22230

 

[murkaboris]

It comes back as "FALSE".
I'm actually thinking that bcs its formated as text its not working but
can't figure it out.
Thanks for trying.
Monika

 

[Dave Peterson]

Try using another column:
=a2&""
(This will be text)
and use that in your pivottable.

Then if your pivottable works ok, then you still have a mixture of numbers and
text in that original field.

Ps.

In xl2003 menus:
Rightclick on the field in the pivottable
select Field settings|Click Advanced
Make sure that the autosort options are what you want.

It comes back as "FALSE".
I'm actually thinking that bcs its formated as text its not working but
can't figure it out.
Thanks for trying.
Monika