Dates Formats in Pivot Table Help

L

Louis Markowski

Hello,

I am creating a pivot table which my data will contain the following.
Columns;
Store
Date
Amount

I have the Store in the Page area, I want to use the date field for various
different lookups.
By Week, By Month, By Year.
I can add these columns to the worksheet but it brings in the standard
format of 2/10/06 even though I have formated it for the month or year. How
can I create these choices within the Pivot table to expand my break down of
the data?

Thank you.
Lou
 
G

Gary L Brown

Formatting doesn't change the underlying value. You should create 3
additional columns in your data. =Month(MyDateField), =Year(MyDateField),
=(WeekNum(MyDateField).
Now, use these columns in your page.

HTH,
 
D

Debra Dalgleish

Temporarily move the date field to the row area
Right-click on the Date field button, and choose Group and Show Detail>Group
Choose to summarize by Years and Months or select Days, and set the
number of days to 7, then select Years.
(you can't group by both weeks and months at the same time)
Click OK
Move the Date fields to the page area
 
G

Gary L Brown

Thanks Debra!!!!!!!!!!!
That's great. I've learned a new one today.
Just checked your website. Good luck on your new book "Excel PivotTables
Recipes". You obviously know your pivot tables :O>.
It's coming out March 6th? Count me in.
 
D

Debra Dalgleish

You're welcome! And I hope you find some other useful tips in the book.

The formulas you showed in your message would help if you needed to show
months and week numbers at the same time. My method wouldn't do that.
 
Top