Pivot table & weeknum query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Within a pivot table, I have summarised the data firstly by month, then by
week, then by work type.
I need to show the 'week commencing' date once for each week displayed.
I had a limited amount of success by adding a column to the data area and
used a formula (from Rick Rothstein - thanks) to display exactly this. When
used in the pivot table however, it was treated as an individual entry rather
than a 'group' heading. I understand why this has happened, but I need a work
around. Even if there's a formula outside the pt which looks at column B
(where weeknumber is) and works out the 1st date of the week from there.
Thanks in advance.
 
If you use a formula to calculate the week start date, it should only
appear once for each week's dates. For example, with dates in column A:

=A2-IF(WEEKDAY(A2,2)=7,0,WEEKDAY(A2,2))

Add that field to the Row area, after the month.
The same WeekStart date may appear under two months, if it overlaps.
 
Back
Top