| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Pivot by Year
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort Pivot by Year
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 "murkaboris" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort Pivot by Year
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 wrote: 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 "murkaboris" wrote: 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 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort Pivot by Year
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" wrote: 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 wrote: 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 "murkaboris" wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort Pivot by Year
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 wrote: 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" wrote: 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 wrote: 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 "murkaboris" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort Pivot by Year
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 "murkaboris" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort Pivot by Year
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. murkaboris wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 "murkaboris" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort Pivot by Year
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" wrote: 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. murkaboris wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 "murkaboris" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort Pivot by Year
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. murkaboris wrote: 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" wrote: 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. murkaboris wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 "murkaboris" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sort by date but not year | Excel Discussion (Misc queries) | |||
| Sort by week and year | Excel Worksheet Functions | |||
| Sort by Year and Week | Excel Worksheet Functions | |||
| Sort by Month and Year | Excel Discussion (Misc queries) | |||
| How do I sort dates by month rather than by year? | Excel Discussion (Misc queries) | |||
Linear Mode
