Pivot table & weeknum query
 

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.

--
Traa Dy Liooar

Jock

Pivot table & weeknum query
 

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.

Jock wrote:
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.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Pivot table & weeknum query
 

Thanks Debra, that's sorted it.
--
Traa Dy Liooar

Jock


"Debra Dalgleish" wrote:

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.

Jock wrote:
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.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html