Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
Pivot Table, MS Query and Oracle | Excel Discussion (Misc queries) |