Pivot Table - working with dates

[Bertmcinster]

I constructed a pivot table to consolodate and show sales by date. The data has the detailed date, ex. 15/01/11. In the pivot table I just want the month and year to show, so Jan 11. Is there a way to adjust the pivot for this?

Thanks for any help in advance!

[Roger Govier]

Hi

I would create 3 Tables.
On Sheet Labour, select cells A1:H4>Insert tab>Table>my table has headers
ON the Design tab that appears give the table the name Labour.

Repeat the procedure for Direct and Equip
(I have already done this in the attached workbook)

Then use Sumproduct to give the values on Cashflow
in C5
 =SUMPRODUCT((Labour[DATE]>=CashFlow!C$1)*(Labour[DATE]<CashFlow!D$1)*(Labour[TOTAL WAGE]+Labour[SUB]))

in C6
=SUMPRODUCT((Direct[DATE]>=CashFlow!C$1)*(Direct[DATE]<CashFlow!D$1)*(Direct[VALUE]))

in C7
=SUMPRODUCT((Equip[Date]>=CashFlow!C$1)*(Equip[Date]<CashFlow!D$1)*(Equip[VALUE]))

One of the many advantages of Tables is that they are fully dynamic and will adjust automatically as you add more data.

Do NOT attempt to copy the formulae across the page by dragging the autofill cross "+"
If you do, the table references will adjust as you copy across.

Instead, copy C5:C7 then select D5:AV% and Paste
The table column references will remain fixed, but the Date references in row 1 will adjust.