Grouping By Date in Pivot Table
Folks,
I am trying to do a pivot table that will use the following data to first sum the subtotal by Product and then Date. In this Case, I want Pivot to Group by Date such as BA for January 2004 is 1,703.44. So, I am creating the Pivot table selecting Product and Date under Rows and then summing by subtotal. From my report, I try to right-mouse click on the Date field and try to select Group. Excel comes back saying that it cannot Group that selection. I am not sure why that's happening. My Date field is of the Date format. Can somebody please help? Thanks. DATE PRODUCT SUBTOTAL 1/1/2004 BA 256.36 1/9/2004 BA 369.36 1/15/2004 BA 482.36 1/29/2004 BA 595.36 3/15/2004 BA 708.36 6/15/2004 BA 821.36 12/31/2004 BA 934.36 1/16/2005 BA 1047.36 1/17/2005 BA 1160.36 1/18/2005 BA 1273.36 1/19/2005 BA 1386.36 03/15/2004 BH 123.36 06/15/2004 BH 123.36 09/15/2004 BH 365.36 1/23/2005 BH 1499.36 2/22/2005 BH 1612.36 3/29/2005 BH 1725.36 5/15/2005 BH 1838.36 |
Grouping By Date in Pivot Table
Not sure why this is happening, but you can add an auxiliary column
Month-Yr with the formula =TEXT(A2, "mmmm-yy") (assuming your dates are in column A:A starting from A2). By editing the source range of the Pivot table you can now use Month-Yr for your grouping. HTH Kostis Vezerides |
Grouping By Date in Pivot Table
Hi Shams
You say that your date column is of valid Excel date formats, but does your range include any blank cells. This will cause Excel to give the message can't group by Date, or any spurious text in a cell will give the same message. Ensure you have dates for every row in your table that is being summarised by the PT, or, make the range dynamic so as to only select a range that contains data. Regards Roger Govier Shams wrote: Folks, I am trying to do a pivot table that will use the following data to first sum the subtotal by Product and then Date. In this Case, I want Pivot to Group by Date such as BA for January 2004 is 1,703.44. So, I am creating the Pivot table selecting Product and Date under Rows and then summing by subtotal. From my report, I try to right-mouse click on the Date field and try to select Group. Excel comes back saying that it cannot Group that selection. I am not sure why that's happening. My Date field is of the Date format. Can somebody please help? Thanks. DATE PRODUCT SUBTOTAL 1/1/2004 BA 256.36 1/9/2004 BA 369.36 1/15/2004 BA 482.36 1/29/2004 BA 595.36 3/15/2004 BA 708.36 6/15/2004 BA 821.36 12/31/2004 BA 934.36 1/16/2005 BA 1047.36 1/17/2005 BA 1160.36 1/18/2005 BA 1273.36 1/19/2005 BA 1386.36 03/15/2004 BH 123.36 06/15/2004 BH 123.36 09/15/2004 BH 365.36 1/23/2005 BH 1499.36 2/22/2005 BH 1612.36 3/29/2005 BH 1725.36 5/15/2005 BH 1838.36 |
Grouping By Date in Pivot Table
The only time I've failed to be able to group by dates is when there's something
in that field that isn't a date--either text or even an empty cell. Any chance??? Shams wrote: Folks, I am trying to do a pivot table that will use the following data to first sum the subtotal by Product and then Date. In this Case, I want Pivot to Group by Date such as BA for January 2004 is 1,703.44. So, I am creating the Pivot table selecting Product and Date under Rows and then summing by subtotal. From my report, I try to right-mouse click on the Date field and try to select Group. Excel comes back saying that it cannot Group that selection. I am not sure why that's happening. My Date field is of the Date format. Can somebody please help? Thanks. DATE PRODUCT SUBTOTAL 1/1/2004 BA 256.36 1/9/2004 BA 369.36 1/15/2004 BA 482.36 1/29/2004 BA 595.36 3/15/2004 BA 708.36 6/15/2004 BA 821.36 12/31/2004 BA 934.36 1/16/2005 BA 1047.36 1/17/2005 BA 1160.36 1/18/2005 BA 1273.36 1/19/2005 BA 1386.36 03/15/2004 BH 123.36 06/15/2004 BH 123.36 09/15/2004 BH 365.36 1/23/2005 BH 1499.36 2/22/2005 BH 1612.36 3/29/2005 BH 1725.36 5/15/2005 BH 1838.36 -- Dave Peterson |
Grouping By Date in Pivot Table
Folks,
I took another look at my dataset and ths time I used a handful of rows to test the Pivot Table ensuing that the Date Field was in date format (m/dd/yyyyy). It still doesn't work. Let me try to paste a snapshot of the Pivot Table Report that is being created: Sum of Subtotal Product Date Total BA 3/28/2005 1979.34 9/28/2004 790.53 10/5/2004 1503.3 10/12/200411274.8 10/19/20041880.69 10/26/20042933.67 11/1/2004 1085.98 11/8/2004 1666.45 11/15/20041853.1 11/22/20041467.86 11/29/20041626.94 12/6/2004 1480.4 12/13/20041689.71 12/20/20042032.68 1/4/2005 3721.89 1/10/2005 1759.75 1/17/2005 2048.5 1/24/2005 3276.52 1/31/2005 1551.58 3/7/2005 2126.97 3/14/2005 2045.91 3/21/2005 2333.56 BA Total 52130.13 BH 3/28/2005 1334.16 4/11/2005 2213.94 4/18/2005 1529.16 4/4/2005 2547.78 9/29/2004 1514.18 10/6/2004 2701.09 10/13/20042218.33 10/20/20042027.16 10/26/20041312.41 11/1/2004 954.15 11/8/2004 945.28 11/15/20042280.78 11/22/20041829.11 11/29/20041150.86 12/6/2004 1182.66 12/13/20042110.05 12/20/20041917.51 1/17/2005 1786.11 1/24/2005 2364.51 1/31/2005 1408.62 BH Total 47952.88 Grand Total 100083.01 I hope you can decipher the unfriendly paste that I did above but that's basically is the Pivot Table I am getting. The Product & Date Fields have drop-down arrows. I am right-mouse clicking on Date and trying to select the Group Function - I still get the error. I have no idea why this is happening..... Could you maybe try to play with a few numbers at your end ensuring both 2004/2005 Dates for BA and BH and see if it groups for you. Thanks for all your help - much appreciated. "Dave Peterson" wrote: The only time I've failed to be able to group by dates is when there's something in that field that isn't a date--either text or even an empty cell. Any chance??? Shams wrote: Folks, I am trying to do a pivot table that will use the following data to first sum the subtotal by Product and then Date. In this Case, I want Pivot to Group by Date such as BA for January 2004 is 1,703.44. So, I am creating the Pivot table selecting Product and Date under Rows and then summing by subtotal. From my report, I try to right-mouse click on the Date field and try to select Group. Excel comes back saying that it cannot Group that selection. I am not sure why that's happening. My Date field is of the Date format. Can somebody please help? Thanks. DATE PRODUCT SUBTOTAL 1/1/2004 BA 256.36 1/9/2004 BA 369.36 1/15/2004 BA 482.36 1/29/2004 BA 595.36 3/15/2004 BA 708.36 6/15/2004 BA 821.36 12/31/2004 BA 934.36 1/16/2005 BA 1047.36 1/17/2005 BA 1160.36 1/18/2005 BA 1273.36 1/19/2005 BA 1386.36 03/15/2004 BH 123.36 06/15/2004 BH 123.36 09/15/2004 BH 365.36 1/23/2005 BH 1499.36 2/22/2005 BH 1612.36 3/29/2005 BH 1725.36 5/15/2005 BH 1838.36 -- Dave Peterson |
Grouping By Date in Pivot Table
I still think it's a problem in the raw data.
I'd spend time looking there first. Shams wrote: Folks, I took another look at my dataset and ths time I used a handful of rows to test the Pivot Table ensuing that the Date Field was in date format (m/dd/yyyyy). It still doesn't work. Let me try to paste a snapshot of the Pivot Table Report that is being created: Sum of Subtotal Product Date Total BA 3/28/2005 1979.34 9/28/2004 790.53 10/5/2004 1503.3 10/12/200411274.8 10/19/20041880.69 10/26/20042933.67 11/1/2004 1085.98 11/8/2004 1666.45 11/15/20041853.1 11/22/20041467.86 11/29/20041626.94 12/6/2004 1480.4 12/13/20041689.71 12/20/20042032.68 1/4/2005 3721.89 1/10/2005 1759.75 1/17/2005 2048.5 1/24/2005 3276.52 1/31/2005 1551.58 3/7/2005 2126.97 3/14/2005 2045.91 3/21/2005 2333.56 BA Total 52130.13 BH 3/28/2005 1334.16 4/11/2005 2213.94 4/18/2005 1529.16 4/4/2005 2547.78 9/29/2004 1514.18 10/6/2004 2701.09 10/13/20042218.33 10/20/20042027.16 10/26/20041312.41 11/1/2004 954.15 11/8/2004 945.28 11/15/20042280.78 11/22/20041829.11 11/29/20041150.86 12/6/2004 1182.66 12/13/20042110.05 12/20/20041917.51 1/17/2005 1786.11 1/24/2005 2364.51 1/31/2005 1408.62 BH Total 47952.88 Grand Total 100083.01 I hope you can decipher the unfriendly paste that I did above but that's basically is the Pivot Table I am getting. The Product & Date Fields have drop-down arrows. I am right-mouse clicking on Date and trying to select the Group Function - I still get the error. I have no idea why this is happening..... Could you maybe try to play with a few numbers at your end ensuring both 2004/2005 Dates for BA and BH and see if it groups for you. Thanks for all your help - much appreciated. "Dave Peterson" wrote: The only time I've failed to be able to group by dates is when there's something in that field that isn't a date--either text or even an empty cell. Any chance??? Shams wrote: Folks, I am trying to do a pivot table that will use the following data to first sum the subtotal by Product and then Date. In this Case, I want Pivot to Group by Date such as BA for January 2004 is 1,703.44. So, I am creating the Pivot table selecting Product and Date under Rows and then summing by subtotal. From my report, I try to right-mouse click on the Date field and try to select Group. Excel comes back saying that it cannot Group that selection. I am not sure why that's happening. My Date field is of the Date format. Can somebody please help? Thanks. DATE PRODUCT SUBTOTAL 1/1/2004 BA 256.36 1/9/2004 BA 369.36 1/15/2004 BA 482.36 1/29/2004 BA 595.36 3/15/2004 BA 708.36 6/15/2004 BA 821.36 12/31/2004 BA 934.36 1/16/2005 BA 1047.36 1/17/2005 BA 1160.36 1/18/2005 BA 1273.36 1/19/2005 BA 1386.36 03/15/2004 BH 123.36 06/15/2004 BH 123.36 09/15/2004 BH 365.36 1/23/2005 BH 1499.36 2/22/2005 BH 1612.36 3/29/2005 BH 1725.36 5/15/2005 BH 1838.36 -- Dave Peterson -- Dave Peterson |
Grouping By Date in Pivot Table
Hi Shams
Looking at the data it appears to be OK. But are all of the dates selected. If you look at the dropdown on Dates, does it include All? Try dragging the Date field out of the PT, then do a Refresh, then drag it back again to see if that helps. Regards Roger Govier Shams wrote: Folks, I took another look at my dataset and ths time I used a handful of rows to test the Pivot Table ensuing that the Date Field was in date format (m/dd/yyyyy). It still doesn't work. Let me try to paste a snapshot of the Pivot Table Report that is being created: Sum of Subtotal Product Date Total BA 3/28/2005 1979.34 9/28/2004 790.53 10/5/2004 1503.3 10/12/200411274.8 10/19/20041880.69 10/26/20042933.67 11/1/2004 1085.98 11/8/2004 1666.45 11/15/20041853.1 11/22/20041467.86 11/29/20041626.94 12/6/2004 1480.4 12/13/20041689.71 12/20/20042032.68 1/4/2005 3721.89 1/10/2005 1759.75 1/17/2005 2048.5 1/24/2005 3276.52 1/31/2005 1551.58 3/7/2005 2126.97 3/14/2005 2045.91 3/21/2005 2333.56 BA Total 52130.13 BH 3/28/2005 1334.16 4/11/2005 2213.94 4/18/2005 1529.16 4/4/2005 2547.78 9/29/2004 1514.18 10/6/2004 2701.09 10/13/20042218.33 10/20/20042027.16 10/26/20041312.41 11/1/2004 954.15 11/8/2004 945.28 11/15/20042280.78 11/22/20041829.11 11/29/20041150.86 12/6/2004 1182.66 12/13/20042110.05 12/20/20041917.51 1/17/2005 1786.11 1/24/2005 2364.51 1/31/2005 1408.62 BH Total 47952.88 Grand Total 100083.01 I hope you can decipher the unfriendly paste that I did above but that's basically is the Pivot Table I am getting. The Product & Date Fields have drop-down arrows. I am right-mouse clicking on Date and trying to select the Group Function - I still get the error. I have no idea why this is happening..... Could you maybe try to play with a few numbers at your end ensuring both 2004/2005 Dates for BA and BH and see if it groups for you. Thanks for all your help - much appreciated. "Dave Peterson" wrote: The only time I've failed to be able to group by dates is when there's something in that field that isn't a date--either text or even an empty cell. Any chance??? Shams wrote: Folks, I am trying to do a pivot table that will use the following data to first sum the subtotal by Product and then Date. In this Case, I want Pivot to Group by Date such as BA for January 2004 is 1,703.44. So, I am creating the Pivot table selecting Product and Date under Rows and then summing by subtotal. From my report, I try to right-mouse click on the Date field and try to select Group. Excel comes back saying that it cannot Group that selection. I am not sure why that's happening. My Date field is of the Date format. Can somebody please help? Thanks. DATE PRODUCT SUBTOTAL 1/1/2004 BA 256.36 1/9/2004 BA 369.36 1/15/2004 BA 482.36 1/29/2004 BA 595.36 3/15/2004 BA 708.36 6/15/2004 BA 821.36 12/31/2004 BA 934.36 1/16/2005 BA 1047.36 1/17/2005 BA 1160.36 1/18/2005 BA 1273.36 1/19/2005 BA 1386.36 03/15/2004 BH 123.36 06/15/2004 BH 123.36 09/15/2004 BH 365.36 1/23/2005 BH 1499.36 2/22/2005 BH 1612.36 3/29/2005 BH 1725.36 5/15/2005 BH 1838.36 -- Dave Peterson |
Grouping By Date in Pivot Table
Thank you very much for all of your inputs. The original data came from a
mainframe file and for some reason was still being referenced with .txt features even though it was transferred into excel. I basically replicated that scenario by first taking my raw data and saving it as comma delimited and then opening it back again in excel..I reformated the Date column as date and ran my Pivot..the Group By Date worked like a charm. I am not sure why it happened but it seemed to have worked. Thanks, again. "Shams" wrote: Folks, I am trying to do a pivot table that will use the following data to first sum the subtotal by Product and then Date. In this Case, I want Pivot to Group by Date such as BA for January 2004 is 1,703.44. So, I am creating the Pivot table selecting Product and Date under Rows and then summing by subtotal. From my report, I try to right-mouse click on the Date field and try to select Group. Excel comes back saying that it cannot Group that selection. I am not sure why that's happening. My Date field is of the Date format. Can somebody please help? Thanks. DATE PRODUCT SUBTOTAL 1/1/2004 BA 256.36 1/9/2004 BA 369.36 1/15/2004 BA 482.36 1/29/2004 BA 595.36 3/15/2004 BA 708.36 6/15/2004 BA 821.36 12/31/2004 BA 934.36 1/16/2005 BA 1047.36 1/17/2005 BA 1160.36 1/18/2005 BA 1273.36 1/19/2005 BA 1386.36 03/15/2004 BH 123.36 06/15/2004 BH 123.36 09/15/2004 BH 365.36 1/23/2005 BH 1499.36 2/22/2005 BH 1612.36 3/29/2005 BH 1725.36 5/15/2005 BH 1838.36 |
Grouping By Date in Pivot Table
It might be easier to do this to convert the text to dates:
Select the range edit|replace what: / (slash) with: / (slash) replace all Excel will see it as a date. Make sure the date is in the same format (mdy or dmy) as your short date format for your pc. Another way is to select the range data|text to columns choose fixed width remove any lines that excel guessed choose mdy (or dmy) to match the data Shams wrote: Thank you very much for all of your inputs. The original data came from a mainframe file and for some reason was still being referenced with .txt features even though it was transferred into excel. I basically replicated that scenario by first taking my raw data and saving it as comma delimited and then opening it back again in excel..I reformated the Date column as date and ran my Pivot..the Group By Date worked like a charm. I am not sure why it happened but it seemed to have worked. Thanks, again. "Shams" wrote: Folks, I am trying to do a pivot table that will use the following data to first sum the subtotal by Product and then Date. In this Case, I want Pivot to Group by Date such as BA for January 2004 is 1,703.44. So, I am creating the Pivot table selecting Product and Date under Rows and then summing by subtotal. From my report, I try to right-mouse click on the Date field and try to select Group. Excel comes back saying that it cannot Group that selection. I am not sure why that's happening. My Date field is of the Date format. Can somebody please help? Thanks. DATE PRODUCT SUBTOTAL 1/1/2004 BA 256.36 1/9/2004 BA 369.36 1/15/2004 BA 482.36 1/29/2004 BA 595.36 3/15/2004 BA 708.36 6/15/2004 BA 821.36 12/31/2004 BA 934.36 1/16/2005 BA 1047.36 1/17/2005 BA 1160.36 1/18/2005 BA 1273.36 1/19/2005 BA 1386.36 03/15/2004 BH 123.36 06/15/2004 BH 123.36 09/15/2004 BH 365.36 1/23/2005 BH 1499.36 2/22/2005 BH 1612.36 3/29/2005 BH 1725.36 5/15/2005 BH 1838.36 -- Dave Peterson |
All times are GMT +1. The time now is 12:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com