Grouping by both Month and by Week in a Pivot Table

  • Thread starter Thread starter Gordon
  • Start date Start date
G

Gordon

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)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping by month in a Pivot Table 1
PivotTable Group by Week and Month 5
Grouping daily transactions in a pivot table by month - CANNOT GROUPTHAT SELECTION ERROR 1
Pivot Table - Group by Date Excel 2007 1
Group by Month in Pivot-table 4
Group in Multiple Pivot table 1
Weekly Pivot Tables 1
Grouping data with Pivot Tables Excel 2003 1

Back
Top