Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable Group by Week and Month
I have created a Pivot Table the is groups my date by Days = 7. I then
wanted to add a Month grouping that would keep the days in the correct month even if the week is split. I have done this in the past with no problem but after upgrading to Office 2003 the pivot table is reverting the Day grouping to 1. Any ideas how to correct this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable Group by Week and Month
Hi Monica
Also on XL2003. It does still group by 7 day periods, even when you add Months (and Quarters), but I agree that the selection box for number of days which becomes greyed out, does revert back to 1, even though my report still does do the grouping in weekly intervals. -- Regards Roger Govier "Monica Hall" wrote in message ... I have created a Pivot Table the is groups my date by Days = 7. I then wanted to add a Month grouping that would keep the days in the correct month even if the week is split. I have done this in the past with no problem but after upgrading to Office 2003 the pivot table is reverting the Day grouping to 1. Any ideas how to correct this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable Group by Week and Month
I'm starting to think I have a bug in my software because my grouping does
revert to 1 day even when I enter 7 then select the Month. I've tried grouping by days only first and it groups just fine. I then go back to the grouping and select month the day box shows 7 but when I hit okay the table changes to Month and 1 day group. Frustating! Monica "Roger Govier" wrote: Hi Monica Also on XL2003. It does still group by 7 day periods, even when you add Months (and Quarters), but I agree that the selection box for number of days which becomes greyed out, does revert back to 1, even though my report still does do the grouping in weekly intervals. -- Regards Roger Govier "Monica Hall" wrote in message ... I have created a Pivot Table the is groups my date by Days = 7. I then wanted to add a Month grouping that would keep the days in the correct month even if the week is split. I have done this in the past with no problem but after upgrading to Office 2003 the pivot table is reverting the Day grouping to 1. Any ideas how to correct this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable Group by Week and Month
Hi Monica
My apologies, for I fear I may have misled you. You do not have a bug in your software. On looking back at my test data, the individual dates did happen to all be the same day of the week, therefore I was "fooled" into thinking the PT was still grouping on a 7 day frequency inside of Month frequency. It does lose the 7 day grouping, once Month or Quarter is applied, and does go back to individual days as you describe. I just tried it on my wife's computer, XL2002 SP3, and that behaves the same. I don't have access to a machine with XL2000 and XL97 just at the moment, so I can't go back to explore the behaviour in those versions. I have tried changing the starting date to every different weekday to see if that makes any difference, but it doesn't, so I'm afraid I have no workaround for you. There may be a VB solution, but I can't think of one. -- Regards Roger Govier "Monica Hall" wrote in message ... I'm starting to think I have a bug in my software because my grouping does revert to 1 day even when I enter 7 then select the Month. I've tried grouping by days only first and it groups just fine. I then go back to the grouping and select month the day box shows 7 but when I hit okay the table changes to Month and 1 day group. Frustating! Monica "Roger Govier" wrote: Hi Monica Also on XL2003. It does still group by 7 day periods, even when you add Months (and Quarters), but I agree that the selection box for number of days which becomes greyed out, does revert back to 1, even though my report still does do the grouping in weekly intervals. -- Regards Roger Govier "Monica Hall" wrote in message ... I have created a Pivot Table the is groups my date by Days = 7. I then wanted to add a Month grouping that would keep the days in the correct month even if the week is split. I have done this in the past with no problem but after upgrading to Office 2003 the pivot table is reverting the Day grouping to 1. Any ideas how to correct this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable Group by Week and Month
I tried a couple of things using VB code. But was unable to get the days to
stick. I don't have a lot of time to spend on this issue but it is driving me crazy. Microsoft help says that you should be able to group multiple days and month. Thank you, Monica "Roger Govier" wrote: Hi Monica My apologies, for I fear I may have misled you. You do not have a bug in your software. On looking back at my test data, the individual dates did happen to all be the same day of the week, therefore I was "fooled" into thinking the PT was still grouping on a 7 day frequency inside of Month frequency. It does lose the 7 day grouping, once Month or Quarter is applied, and does go back to individual days as you describe. I just tried it on my wife's computer, XL2002 SP3, and that behaves the same. I don't have access to a machine with XL2000 and XL97 just at the moment, so I can't go back to explore the behaviour in those versions. I have tried changing the starting date to every different weekday to see if that makes any difference, but it doesn't, so I'm afraid I have no workaround for you. There may be a VB solution, but I can't think of one. -- Regards Roger Govier "Monica Hall" wrote in message ... I'm starting to think I have a bug in my software because my grouping does revert to 1 day even when I enter 7 then select the Month. I've tried grouping by days only first and it groups just fine. I then go back to the grouping and select month the day box shows 7 but when I hit okay the table changes to Month and 1 day group. Frustating! Monica "Roger Govier" wrote: Hi Monica Also on XL2003. It does still group by 7 day periods, even when you add Months (and Quarters), but I agree that the selection box for number of days which becomes greyed out, does revert back to 1, even though my report still does do the grouping in weekly intervals. -- Regards Roger Govier "Monica Hall" wrote in message ... I have created a Pivot Table the is groups my date by Days = 7. I then wanted to add a Month grouping that would keep the days in the correct month even if the week is split. I have done this in the past with no problem but after upgrading to Office 2003 the pivot table is reverting the Day grouping to 1. Any ideas how to correct this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable Group by Week and Month
Hi Monica
I have tried again and also failed. The only workaround that I can think of at the moment is perhaps to have another column in your source table, and use the Weeknum() feature from the Analysis Toolpack. =Weeknum(your_date) Then allocate this field to the PT as well, and you would see data grouped by Weeknum inside Month. If Debra Dalgleish spots this post, she may well be able to come up with a "correct" solution for you. -- Regards Roger Govier "Monica Hall" wrote in message ... I tried a couple of things using VB code. But was unable to get the days to stick. I don't have a lot of time to spend on this issue but it is driving me crazy. Microsoft help says that you should be able to group multiple days and month. Thank you, Monica "Roger Govier" wrote: Hi Monica My apologies, for I fear I may have misled you. You do not have a bug in your software. On looking back at my test data, the individual dates did happen to all be the same day of the week, therefore I was "fooled" into thinking the PT was still grouping on a 7 day frequency inside of Month frequency. It does lose the 7 day grouping, once Month or Quarter is applied, and does go back to individual days as you describe. I just tried it on my wife's computer, XL2002 SP3, and that behaves the same. I don't have access to a machine with XL2000 and XL97 just at the moment, so I can't go back to explore the behaviour in those versions. I have tried changing the starting date to every different weekday to see if that makes any difference, but it doesn't, so I'm afraid I have no workaround for you. There may be a VB solution, but I can't think of one. -- Regards Roger Govier "Monica Hall" wrote in message ... I'm starting to think I have a bug in my software because my grouping does revert to 1 day even when I enter 7 then select the Month. I've tried grouping by days only first and it groups just fine. I then go back to the grouping and select month the day box shows 7 but when I hit okay the table changes to Month and 1 day group. Frustating! Monica "Roger Govier" wrote: Hi Monica Also on XL2003. It does still group by 7 day periods, even when you add Months (and Quarters), but I agree that the selection box for number of days which becomes greyed out, does revert back to 1, even though my report still does do the grouping in weekly intervals. -- Regards Roger Govier "Monica Hall" wrote in message ... I have created a Pivot Table the is groups my date by Days = 7. I then wanted to add a Month grouping that would keep the days in the correct month even if the week is split. I have done this in the past with no problem but after upgrading to Office 2003 the pivot table is reverting the Day grouping to 1. Any ideas how to correct this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Group by Week? | New Users to Excel | |||
Show week number in current month | Excel Discussion (Misc queries) | |||
Convert financial week into corresponding month | Excel Discussion (Misc queries) | |||
calculate month from week number | Excel Worksheet Functions | |||
Convert week number into calendar month? | Excel Worksheet Functions |