Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
I have a huge list(24808RX14C) forming a pivot table. For some reason, when
I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
BTW, I am in Excel 2003.
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
If you have any text or empty cells in that date field, you won't be able to
group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of
the data and its sum was zero, so it's safe to assume there are no blanks anywhere in the data. I also multiplied the date field by 2 and then averaged the column (since this would return a #value error if any of the dates were text) and got a valid date back. Therefore, I think it's safe to assume that the conditions you specified for failure are not met. I've also noticed that there is a (blank) option under every field-arrow in the pivot table. "Dave Peterson" wrote: If you have any text or empty cells in that date field, you won't be able to group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
Check your range. As a guess your pivot table includes blank cells at the end
of your data range. Pull up th epivot table wizard and hit back to access the Data Range and check that it includes only non blank data. -- HTH... Jim Thomlinson "mattgoof2005" wrote: I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of the data and its sum was zero, so it's safe to assume there are no blanks anywhere in the data. I also multiplied the date field by 2 and then averaged the column (since this would return a #value error if any of the dates were text) and got a valid date back. Therefore, I think it's safe to assume that the conditions you specified for failure are not met. I've also noticed that there is a (blank) option under every field-arrow in the pivot table. "Dave Peterson" wrote: If you have any text or empty cells in that date field, you won't be able to group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
Jim, that was my first guess. I had originally used a named list, so I tried
converting it back to a range and defining it by $A$1:$O$24807, but I still get the same error. "Jim Thomlinson" wrote: Check your range. As a guess your pivot table includes blank cells at the end of your data range. Pull up th epivot table wizard and hit back to access the Data Range and check that it includes only non blank data. -- HTH... Jim Thomlinson "mattgoof2005" wrote: I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of the data and its sum was zero, so it's safe to assume there are no blanks anywhere in the data. I also multiplied the date field by 2 and then averaged the column (since this would return a #value error if any of the dates were text) and got a valid date back. Therefore, I think it's safe to assume that the conditions you specified for failure are not met. I've also noticed that there is a (blank) option under every field-arrow in the pivot table. "Dave Peterson" wrote: If you have any text or empty cells in that date field, you won't be able to group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
As another test, I just tried making it smaller and cut it all the way to 2
rows of data and still could not group the dates. Unless the 25 different 2-row combinations I tried were all corrupt, I have absolutely no idea what's going on. "mattgoof2005" wrote: Jim, that was my first guess. I had originally used a named list, so I tried converting it back to a range and defining it by $A$1:$O$24807, but I still get the same error. "Jim Thomlinson" wrote: Check your range. As a guess your pivot table includes blank cells at the end of your data range. Pull up th epivot table wizard and hit back to access the Data Range and check that it includes only non blank data. -- HTH... Jim Thomlinson "mattgoof2005" wrote: I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of the data and its sum was zero, so it's safe to assume there are no blanks anywhere in the data. I also multiplied the date field by 2 and then averaged the column (since this would return a #value error if any of the dates were text) and got a valid date back. Therefore, I think it's safe to assume that the conditions you specified for failure are not met. I've also noticed that there is a (blank) option under every field-arrow in the pivot table. "Dave Peterson" wrote: If you have any text or empty cells in that date field, you won't be able to group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
Using a number stored as text in a calculation (a1*2/2) will coerce the value to
a number in that calculation. So using =isnumber() would be a better test. IIRC, I've copied the data to another worksheet and recreated the pivottable and it's worked ok. (I think!) Sometimes, excel seemed to remember that a cell used to be text and just wouldn't let go--(It's been a long time, but I think that was what worked.) mattgoof2005 wrote: I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of the data and its sum was zero, so it's safe to assume there are no blanks anywhere in the data. I also multiplied the date field by 2 and then averaged the column (since this would return a #value error if any of the dates were text) and got a valid date back. Therefore, I think it's safe to assume that the conditions you specified for failure are not met. I've also noticed that there is a (blank) option under every field-arrow in the pivot table. "Dave Peterson" wrote: If you have any text or empty cells in that date field, you won't be able to group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
I just tried the isnumber thing. I think excel might just be being stupid
with text here. "Dave Peterson" wrote: Using a number stored as text in a calculation (a1*2/2) will coerce the value to a number in that calculation. So using =isnumber() would be a better test. IIRC, I've copied the data to another worksheet and recreated the pivottable and it's worked ok. (I think!) Sometimes, excel seemed to remember that a cell used to be text and just wouldn't let go--(It's been a long time, but I think that was what worked.) mattgoof2005 wrote: I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of the data and its sum was zero, so it's safe to assume there are no blanks anywhere in the data. I also multiplied the date field by 2 and then averaged the column (since this would return a #value error if any of the dates were text) and got a valid date back. Therefore, I think it's safe to assume that the conditions you specified for failure are not met. I've also noticed that there is a (blank) option under every field-arrow in the pivot table. "Dave Peterson" wrote: If you have any text or empty cells in that date field, you won't be able to group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
I don't know what that means.
mattgoof2005 wrote: I just tried the isnumber thing. I think excel might just be being stupid with text here. "Dave Peterson" wrote: Using a number stored as text in a calculation (a1*2/2) will coerce the value to a number in that calculation. So using =isnumber() would be a better test. IIRC, I've copied the data to another worksheet and recreated the pivottable and it's worked ok. (I think!) Sometimes, excel seemed to remember that a cell used to be text and just wouldn't let go--(It's been a long time, but I think that was what worked.) mattgoof2005 wrote: I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of the data and its sum was zero, so it's safe to assume there are no blanks anywhere in the data. I also multiplied the date field by 2 and then averaged the column (since this would return a #value error if any of the dates were text) and got a valid date back. Therefore, I think it's safe to assume that the conditions you specified for failure are not met. I've also noticed that there is a (blank) option under every field-arrow in the pivot table. "Dave Peterson" wrote: If you have any text or empty cells in that date field, you won't be able to group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
I used =isnumber() to check the dates, and they are all numbers. You said
earlier that you've had trouble with Excel hanging onto the text formatting, which I think is probably the case. "Dave Peterson" wrote: I don't know what that means. mattgoof2005 wrote: I just tried the isnumber thing. I think excel might just be being stupid with text here. "Dave Peterson" wrote: Using a number stored as text in a calculation (a1*2/2) will coerce the value to a number in that calculation. So using =isnumber() would be a better test. IIRC, I've copied the data to another worksheet and recreated the pivottable and it's worked ok. (I think!) Sometimes, excel seemed to remember that a cell used to be text and just wouldn't let go--(It's been a long time, but I think that was what worked.) mattgoof2005 wrote: I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of the data and its sum was zero, so it's safe to assume there are no blanks anywhere in the data. I also multiplied the date field by 2 and then averaged the column (since this would return a #value error if any of the dates were text) and got a valid date back. Therefore, I think it's safe to assume that the conditions you specified for failure are not met. I've also noticed that there is a (blank) option under every field-arrow in the pivot table. "Dave Peterson" wrote: If you have any text or empty cells in that date field, you won't be able to group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
Ahh.
mattgoof2005 wrote: I used =isnumber() to check the dates, and they are all numbers. You said earlier that you've had trouble with Excel hanging onto the text formatting, which I think is probably the case. "Dave Peterson" wrote: I don't know what that means. mattgoof2005 wrote: I just tried the isnumber thing. I think excel might just be being stupid with text here. "Dave Peterson" wrote: Using a number stored as text in a calculation (a1*2/2) will coerce the value to a number in that calculation. So using =isnumber() would be a better test. IIRC, I've copied the data to another worksheet and recreated the pivottable and it's worked ok. (I think!) Sometimes, excel seemed to remember that a cell used to be text and just wouldn't let go--(It's been a long time, but I think that was what worked.) mattgoof2005 wrote: I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of the data and its sum was zero, so it's safe to assume there are no blanks anywhere in the data. I also multiplied the date field by 2 and then averaged the column (since this would return a #value error if any of the dates were text) and got a valid date back. Therefore, I think it's safe to assume that the conditions you specified for failure are not met. I've also noticed that there is a (blank) option under every field-arrow in the pivot table. "Dave Peterson" wrote: If you have any text or empty cells in that date field, you won't be able to group it. mattgoof2005 wrote: I have a huge list(24808RX14C) forming a pivot table. For some reason, when I right-click on the dates and go to grouping, it says "Cannot group this selection." There is only 1 date in each month (cost collection day), but I would like to group it by Year-Month. Any ideas on why this is happening? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
"Dave Peterson" wrote:
IIRC, I've copied the data to another worksheet and recreated the pivottable and it's worked ok. (I think!) I've had the same problem. Starting a brand new pivot table works, but re-using the existing pivot table (even if you remove all the fields and start from scratch) doesn't work. I'm hoping there's a solution. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can't Group Dates in Pivot Table
If you don't have blank cells or text in the date column, there may be a
grouped field left over from a previous time that you grouped the data. Check the field list, to see if there's a second copy of the date field, e.g. Date2. If there is, add it to the row area, and ungroup it. Then try to group the dates again. Ray Elias wrote: "Dave Peterson" wrote: IIRC, I've copied the data to another worksheet and recreated the pivottable and it's worked ok. (I think!) I've had the same problem. Starting a brand new pivot table works, but re-using the existing pivot table (even if you remove all the fields and start from scratch) doesn't work. I'm hoping there's a solution. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to group a pivot table | Excel Discussion (Misc queries) | |||
Pivot table - group dates per week or month | Excel Discussion (Misc queries) | |||
Why aren't dates always available to group in pivot tables? | Charts and Charting in Excel | |||
Pivot Table, can't group | Excel Discussion (Misc queries) | |||
Can't group by dates in Excel 2003 pivot table | Excel Discussion (Misc queries) |