software

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.
LouFormatting 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,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.quot;Louis Markowskiquot; wrote:

gt; Hello,
gt;
gt; I am creating a pivot table which my data will contain the following.
gt; Columns;
gt; Store
gt; Date
gt; Amount
gt;
gt; I have the Store in the Page area, I want to use the date field for various
gt; different lookups.
gt; By Week, By Month, By Year.
gt; I can add these columns to the worksheet but it brings in the standard
gt; format of 2/10/06 even though I have formated it for the month or year. How
gt; can I create these choices within the Pivot table to expand my break down of
gt; the data?
gt;
gt; Thank you.
gt; Lou
gt;

Temporarily move the date field to the row area
Right-click on the Date field button, and choose Group and Show Detailgt;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

Louis Markowski wrote:
gt; Hello,
gt;
gt; I am creating a pivot table which my data will contain the following.
gt; Columns;
gt; Store
gt; Date
gt; Amount
gt;
gt; I have the Store in the Page area, I want to use the date field for various
gt; different lookups.
gt; By Week, By Month, By Year.
gt; I can add these columns to the worksheet but it brings in the standard
gt; format of 2/10/06 even though I have formated it for the month or year. How
gt; can I create these choices within the Pivot table to expand my break down of
gt; the data?
gt;
gt; Thank you.
gt; Lou
gt;--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.htmlThanks Debra!!!!!!!!!!!
That's great. I've learned a new one today.
Just checked your website. Good luck on your new book quot;Excel PivotTables
Recipesquot;. You obviously know your pivot tables :Ogt;.
It's coming out March 6th? Count me in.
--
Gary Brown

quot;Debra Dalgleishquot; wrote:

gt; Temporarily move the date field to the row area
gt; Right-click on the Date field button, and choose Group and Show Detailgt;Group
gt; Choose to summarize by Years and Months or select Days, and set the
gt; number of days to 7, then select Years.
gt; (you can't group by both weeks and months at the same time)
gt; Click OK
gt; Move the Date fields to the page area
gt;
gt; Louis Markowski wrote:
gt; gt; Hello,
gt; gt;
gt; gt; I am creating a pivot table which my data will contain the following.
gt; gt; Columns;
gt; gt; Store
gt; gt; Date
gt; gt; Amount
gt; gt;
gt; gt; I have the Store in the Page area, I want to use the date field for various
gt; gt; different lookups.
gt; gt; By Week, By Month, By Year.
gt; gt; I can add these columns to the worksheet but it brings in the standard
gt; gt; format of 2/10/06 even though I have formated it for the month or year. How
gt; gt; can I create these choices within the Pivot table to expand my break down of
gt; gt; the data?
gt; gt;
gt; gt; Thank you.
gt; gt; Lou
gt; gt;
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Excel FAQ, Tips amp; Book List
gt; www.contextures.com/tiptech.html
gt;
gt;

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.

Gary L Brown wrote:
gt; Thanks Debra!!!!!!!!!!!
gt; That's great. I've learned a new one today.
gt; Just checked your website. Good luck on your new book quot;Excel PivotTables
gt; Recipesquot;. You obviously know your pivot tables :Ogt;.
gt; It's coming out March 6th? Count me in.--
Debra Dalgleish
Excel FAQ, Tips amp; Book List
www.contextures.com/tiptech.html