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

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