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

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

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

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

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

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

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

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

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