Convert date field to month in Pivot table

[Guest]

I have a Table wich has date fileds,
Date Order No. Qty Shipped
01-27-07
03-19-07
03-20-07

How do I go about to create a pivot table that groups this by month on the
Page Filed of the pivot table.

 

[Dave Peterson]

Put the date in a row field and then do the grouping.

Then drag that row field to the page field.

 

[Guest]

Is there a way of doing this whitout changing the source data?

 

[Dave Peterson]

I didn't suggest changing the data source.

Is there a way of doing this whitout changing the source data?

 

[Guest]

Ok done that but still can't group by month, it is grouping by day's,

02-19-07 Total

02-21-07 Total

03-01-07 Total

03-05-07 Total

 

[Guest]

Hey there. I'm looking to do the exact same thing. I tried reformatting
and/or parsing out the date (mid, etc), of course that doesn't work. I'm
hoping someone can assist. If not i'm not giving up and will post should i
arrive at a solution.

Ok done that but still can't group by month, it is grouping by day's,

02-19-07 Total

02-21-07 Total

03-01-07 Total

03-05-07 Total

 

[Guest]

I finally had to modify the Source and put another coulum with this formula.

=IF(TEXT(J13),"mmm")

Where J13 is the cell were i have the date, this will return the value of
"Mar", "Apr"

 

[Guest]

Ok, think i figured it out. When you create your pivot table drag the date
field to the row area, then use the 'group' pivot option. Worked for me,
hope it helps you.

Ok done that but still can't group by month, it is grouping by day's,

02-19-07 Total

02-21-07 Total

03-01-07 Total

03-05-07 Total

 

[David Biddulph]

Are you sure that formula does what you say? In fact, are you sure that the
formula is valid syntax for doing anything?

Did you perhaps mean to say =TEXT(J13,"mmm") ?

 

[Guest]

I did this by creating the pivot table using the date as a row field. The
right click on the field heading, select Group and Outline, then group. You
should have an option list at the bottom of the Grouping window that allows
you to group by Seconds, Minutes, Hours, Months, Quarters and Years. You can
even select multiple options - like Month and Quarter.

The field has to be a valid date in order to work.

Lauri S.