| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert date field to month in Pivot table
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert date field to month in Pivot table
Put the date in a row field and then do the grouping.
Then drag that row field to the page field. MESTRELLA29 wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert date field to month in Pivot table
Is there a way of doing this whitout changing the source data?
"Dave Peterson" wrote: Put the date in a row field and then do the grouping. Then drag that row field to the page field. MESTRELLA29 wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert date field to month in Pivot table
I didn't suggest changing the data source.
MESTRELLA29 wrote: Is there a way of doing this whitout changing the source data? "Dave Peterson" wrote: Put the date in a row field and then do the grouping. Then drag that row field to the page field. MESTRELLA29 wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert date field to month in Pivot table
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 "Dave Peterson" wrote: I didn't suggest changing the data source. MESTRELLA29 wrote: Is there a way of doing this whitout changing the source data? "Dave Peterson" wrote: Put the date in a row field and then do the grouping. Then drag that row field to the page field. MESTRELLA29 wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert date field to month in Pivot table
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. "MESTRELLA29" wrote: 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 "Dave Peterson" wrote: I didn't suggest changing the data source. MESTRELLA29 wrote: Is there a way of doing this whitout changing the source data? "Dave Peterson" wrote: Put the date in a row field and then do the grouping. Then drag that row field to the page field. MESTRELLA29 wrote: 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 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert date field to month in Pivot table
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" "Suzanne" wrote: 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. "MESTRELLA29" wrote: 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 "Dave Peterson" wrote: I didn't suggest changing the data source. MESTRELLA29 wrote: Is there a way of doing this whitout changing the source data? "Dave Peterson" wrote: Put the date in a row field and then do the grouping. Then drag that row field to the page field. MESTRELLA29 wrote: 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert date field to month in Pivot table
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. "MESTRELLA29" wrote: 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 "Dave Peterson" wrote: I didn't suggest changing the data source. MESTRELLA29 wrote: Is there a way of doing this whitout changing the source data? "Dave Peterson" wrote: Put the date in a row field and then do the grouping. Then drag that row field to the page field. MESTRELLA29 wrote: 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert date field to month in Pivot table
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") ? -- David Biddulph "MESTRELLA29" wrote in message ... 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" "Suzanne" wrote: 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. "MESTRELLA29" wrote: 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 "Dave Peterson" wrote: I didn't suggest changing the data source. MESTRELLA29 wrote: Is there a way of doing this whitout changing the source data? "Dave Peterson" wrote: Put the date in a row field and then do the grouping. Then drag that row field to the page field. MESTRELLA29 wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert date field to month in Pivot table
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. "MESTRELLA29" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Summin Pivot Table data from a date selected in the Page field | Excel Discussion (Misc queries) | |||
| how to change date formating-grouped by month in pivot table | Excel Discussion (Misc queries) | |||
| Calculated Pivot table field on YTD £(subract Mar-feb)=month | Excel Worksheet Functions | |||
| pivot table date field | Excel Worksheet Functions | |||
| Pivot table will not group a date field | Excel Discussion (Misc queries) | |||
Linear Mode
