Posted to microsoft.public.excel.worksheet.functions
|
|
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
|