Dates Formats in Pivot Table Help
 

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

Dates Formats in Pivot Table Help
 

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

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Louis Markowski" wrote:

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

Dates Formats in Pivot Table Help
 

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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Dates Formats in Pivot Table Help
 

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




"Debra Dalgleish" wrote:

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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Dates Formats in Pivot Table Help
 

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:
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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html