If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouping by both Month and by Week in a Pivot Table
Hi,
I'd like to group my date field by Month then by week. I can group by week on its own (by choosing day Data, Group and Outline, Group and changing the value of the number of days from 1 to 7). However if I try to group by month the weeks go back to days. Anyone know the fix. Thanks in Advance Gordon |
#2
|
|||
|
|||
Excel changes back to 1 Day because many of the weeks would be across
months. For example Aug 29-Sept 4 starts in August, but ends in September, so its values should be split between those months. You could create a new column in your data table, and calculate the week for each record. Add that field to the pivot table, and it will appear under separate months where required. To calculate the start date of the week, with dates in column A: =A2-CHOOSE(WEEKDAY(A2),0,1,2,3,4,5,6) Gordon wrote: I'd like to group my date field by Month then by week. I can group by week on its own (by choosing day Data, Group and Outline, Group and changing the value of the number of days from 1 to 7). However if I try to group by month the weeks go back to days. Anyone know the fix. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Month Data in a Pivot Table? | Kent | General Discussion | 3 | July 14th, 2004 01:32 AM |
Pivot Table: Misordered date Grouping | Mark G. | General Discussion | 3 | July 6th, 2004 09:26 PM |