Convert date field to month in Pivot table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MESTRELLA29 - 20 Mar 2007 15:55 GMT

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.

Reply to this Message

Dave Peterson - 20 Mar 2007 16:22 GMT

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

Then drag that row field to the page field.

> I have a Table wich has date fileds,
> Date Order No. Qty Shipped
[quoted text clipped - 4 lines]
> How do I go about to create a pivot table that groups this by month on the
> Page Filed of the pivot table.

Signature

Dave Peterson

Reply to this Message

MESTRELLA29 - 20 Mar 2007 17:16 GMT

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

> Put the date in a row field and then do the grouping.
>
[quoted text clipped - 8 lines]
> > How do I go about to create a pivot table that groups this by month on the
> > Page Filed of the pivot table.

Reply to this Message

Dave Peterson - 20 Mar 2007 18:02 GMT

I didn't suggest changing the data source.

> Is there a way of doing this whitout changing the source data?
>
[quoted text clipped - 14 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

MESTRELLA29 - 20 Mar 2007 18:13 GMT

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

> I didn't suggest changing the data source.
>
[quoted text clipped - 16 lines]
> > >
> > > Dave Peterson

Reply to this Message

Suzanne - 22 Mar 2007 18:03 GMT

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,
>
[quoted text clipped - 26 lines]
> > > >
> > > > Dave Peterson

Reply to this Message

MESTRELLA29 - 22 Mar 2007 18:12 GMT

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"

> 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
[quoted text clipped - 31 lines]
> > > > >
> > > > > Dave Peterson

Reply to this Message

David Biddulph - 22 Mar 2007 19:02 GMT

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") ?

Signature

David Biddulph

>I finally had to modify the Source and put another coulum with this
>formula.
[quoted text clipped - 3 lines]
> Where J13 is the cell were i have the date, this will return the value of
> "Mar", "Apr"

>> 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
[quoted text clipped - 33 lines]
>> > > > >
>> > > > > Dave Peterson

Reply to this Message

Suzanne - 22 Mar 2007 18:13 GMT

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,
>
[quoted text clipped - 26 lines]
> > > >
> > > > Dave Peterson

Reply to this Message

LauriS - 23 May 2007 14:50 GMT

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.

Reply to this Message