Pivot table group dates

I have a pivot table based on a medium sized set of data (5500 rows). In the 
left column of the pivot table I have regular dates wich gives me almost the 
same number of row in the pivot table. When I try to group the date field, I 
get the error message "Cannot group that selection". I have no blanks in the 
field of date. On some other similar pivot tables it work but for some reason 
not this one... What causes this and how do I get past it?
Thanks in advance,
RDW 
0
RDWirr (2)
7/20/2006 2:16:01 AM
excel 39879 articles. 2 followers. Follow

3 Replies
578 Views

Similar Articles

[PageSpeed] 1

Do you have any text in that field?

Are you sure the range you used to build the pivottable didn't go past what you
think is the last row?

When I'm sure I did everything ok, but excel is sure I didn't, I'll rebuild the
pivottable.  Sometimes that works for me.



RD Wirr wrote:
> 
> I have a pivot table based on a medium sized set of data (5500 rows). In the
> left column of the pivot table I have regular dates wich gives me almost the
> same number of row in the pivot table. When I try to group the date field, I
> get the error message "Cannot group that selection". I have no blanks in the
> field of date. On some other similar pivot tables it work but for some reason
> not this one... What causes this and how do I get past it?
> Thanks in advance,
> RDW

-- 

Dave Peterson
0
petersod (12005)
7/20/2006 2:36:08 AM
Thanks Dave,

Sure enough it was text in the date field. Hard to find sometimes. Thanks 
for the tips.

Rgds,
RD Wirr

"Dave Peterson" wrote:

> Do you have any text in that field?
> 
> Are you sure the range you used to build the pivottable didn't go past what you
> think is the last row?
> 
> When I'm sure I did everything ok, but excel is sure I didn't, I'll rebuild the
> pivottable.  Sometimes that works for me.
> 
> 
> 
> RD Wirr wrote:
> > 
> > I have a pivot table based on a medium sized set of data (5500 rows). In the
> > left column of the pivot table I have regular dates wich gives me almost the
> > same number of row in the pivot table. When I try to group the date field, I
> > get the error message "Cannot group that selection". I have no blanks in the
> > field of date. On some other similar pivot tables it work but for some reason
> > not this one... What causes this and how do I get past it?
> > Thanks in advance,
> > RDW
> 
> -- 
> 
> Dave Peterson
> 
0
RDWirr (2)
7/22/2006 5:16:02 PM
If it happens again, you could at least check to see how many numbers are in a
range (and dates are just numbers).

=count(a2:a9999)

And compare that with the number of cells in that range (9998 in my example).

RD Wirr wrote:
> 
> Thanks Dave,
> 
> Sure enough it was text in the date field. Hard to find sometimes. Thanks
> for the tips.
> 
> Rgds,
> RD Wirr
> 
> "Dave Peterson" wrote:
> 
> > Do you have any text in that field?
> >
> > Are you sure the range you used to build the pivottable didn't go past what you
> > think is the last row?
> >
> > When I'm sure I did everything ok, but excel is sure I didn't, I'll rebuild the
> > pivottable.  Sometimes that works for me.
> >
> >
> >
> > RD Wirr wrote:
> > >
> > > I have a pivot table based on a medium sized set of data (5500 rows). In the
> > > left column of the pivot table I have regular dates wich gives me almost the
> > > same number of row in the pivot table. When I try to group the date field, I
> > > get the error message "Cannot group that selection". I have no blanks in the
> > > field of date. On some other similar pivot tables it work but for some reason
> > > not this one... What causes this and how do I get past it?
> > > Thanks in advance,
> > > RDW
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12005)
7/22/2006 7:05:28 PM
Reply:

Similar Artilces:

Deleting employees
We loaded employees into Great Plains with e-connect 8.0.2.1 using taCreateEmployee and taCreateEmployeeAddress. I need to wipe this data out and re-load. I know that with the new version of e-connect there is a taDeleteEmployee but for now can someone tell me the tables I need to truncate out in order to re-run the taCreateEmployee and taCreateEmployeeAddress? thanks -- AHIhelp AHIhelp: The tables are listed below - these are the ones handled by the Employee Delete procedure so I would assume these are the ones you would need to manually handle now as well UPR00100 UPR00102 UPR00...

Multi Table Queries
At my job, I use enter data through generated queries. However it appears that I can only ENTER data in a query when it is based on two tables. If it is three or more, it doesn't let me enter any info. Is this normal? Am I doing something wrong? -Pete Pete wrote: >At my job, I use enter data through generated queries. However it appears >that I can only ENTER data in a query when it is based on two tables. If it >is three or more, it doesn't let me enter any info. > >Is this normal? Am I doing something wrong? Yes, that's common. You should only edit data ...

typing in modeless dialog -> gets intercepted by accelerator table
I have an MFC application with a regular Accelerator Table in a resource. I have chosen for shortcuts containing no CTRL or SHIFT or ALT... since that makes for a fast workflow. This technique is used in many CAD & 3D-applications. However, when I use a modeless dialog (in fact, a dialog in a docked sidebar) and I want to type text into it, the accelerator keys get priority and thus prevent certain characters to be typed... I thought that I could use PreTranslateMessage to catch them before they are sent to the MainFrame, which I can, but at that time, they are also catched from ar...

sales line detail window-after updating the request Ship Date-also
I am also using manufacturing, when you change the requested ship date, you get a window asking: You can update the In House due date, What is the name of this window in modifer? I would like to modify this form, so the No button is disables, I want to make sure they always click 'yes' How do I find this form in modifer? the form name is just 'Microsoft GP Dynamics' thanks -- Doug ...

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

Employee Master Table error after V10 SP3
i have updated V10 to SP3, from SP1, and all seems to be fine except if I try to access the employee master table. Either from cards>payroll>Employee, or payroll transaction entry. Anytime I need to do a lookup on an employee id. Here is the message: A Get Change operation on table UPR_MSTR failed accessing SQL Data If I go to the more information or details button: [Microsoft][SQL Native Client][SQLServer] Invalid column name 'EMPLSUFF' [Microsoft][SQL Native Client][SQLServer] Invalid column name 'Dex_Row_TS' -- Doug It looks like an upgrade script failed...

Posting Date used in Revenue Expense Deferral
I have a PM Invoice with Document Date 16/11/2009 and Posting Date of 01/12/2009. Entered Deferral details starting 01/12/2009. In GL the deferral charges commence 01/12/2009 and are all as expected. The Credit entry for the full invoice is posted to GL with posting date of 01/12/2009. The problem I have is that the Debit entry for the full invoice amount is posted to the document date of 16/11/2009 and I want it to be the same as the invoice posting date (01/12/2009) Is there a way to do this? Thanks Audrey ...

Grouping Pie Slices
Is there a way that I can select and group individual pie slices together? Hi, Jon shows away of doing this in his article. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=471 Cheers Andy beeemergyrl wrote: > Is there a way that I can select and group individual pie slices together? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Can excell notify me of due dates?
If I have due dates on my worksheet can I set something up so that I am notified on these dates? I've used this to help me. http://www.contextures.com/xlCondFormat01.html "Deion007" wrote: > If I have due dates on my worksheet can I set something up so that I am > notified on these dates? ...

Pivot table novice
As a teacher we tried a new data analysis tool this year in my school which worked really well... except for the fact that it meant manually filling in a table. I'm sure there must be a more efficient electronic way of doing it, but I'm not really an expert on these things. I want to be able to put in a list of data: pupils names with scores from two consecutive tests. Then I want to produce a table with test 1 and test 2 as the headers, and pupils' initials appearing in the relevant box. I have managed to create a Pivot Table to show what I want, except of course, it total...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

Excel date changes
I have created a spreadsheet using excel which requires the use dates on a monthly basis (similar to a calendar). The problem I have is that every month I have to go in and change the dates on the spreadsheet to reflect those of the current month before printing. Is there a way I can make it so that excel does that for me every month. Thanks, I'd really appreciate the input. Please elaborate on what the spreadsheet looks like. "phonescoop@hotmail.com" wrote: > I have created a spreadsheet using excel which requires the use dates > on a monthly basis (similar to a cal...

Dates #12
I have a rather looooong list of dates (mm/dd/yy). In a column beside it I want just month and year so I can, for example, find all books sold in a month range. Can you help? NO need where rngA has your dates and rngB is a count of the sales =sumproduct((year(rngA)=2004)*(month(rngA)=8)*(rngB)) to sum rngC if it had the price total for each date =sumproduct((year(rngA)=2004)*(month(rngA)=8)*rngC) -- Don Guillett SalesAid Software donaldb@281.com "Lyndie" <anonymous@discussions.microsoft.com> wrote in message news:83fd01c48543$7d211680$a501280a@phx.gbl... > I have a ...

Grouping mail by From and To
I would like to be able to create a view which groups email messages by sender AND recipient. In other words I would like each group heading for a given name to contain all the email sent to that name plus all the emails received from that name. Seems like an obvious thing to want to do but is it possible and, if so, how? I don't think you can group by two fields like that (either field having a common value). You could create a search folder that will group mail from & to certain people that that's limited to one contract per search folder <john@antell.org> wrote in ...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

can i see the date the last time a cell was changed?
I am trying to figure out a formulla to make the date appear in one cell everytime anouther cell's data is chaged. Use a worksheet_change event to copy the cell address and put in a date stamp. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JohnNuTek" <JohnNuTek@discussions.microsoft.com> wrote in message news:5085A544-CBF4-4B81-A244-B03DE9A0E9E6@microsoft.com... >I am trying to figure out a formulla to make the date appear in one cell > everytime anouther cell's data is chaged. There really isn't a worksheet formula to do that. Typicall...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

Help to build a table
Please help me to build a table similar to this one The number on the first column will change after 16 times, it will go in sequence from 1001, 1002, etc… The number in the second column will change after 4 times and it will go from 1 to 4 The number in the 3rd column will go from 1 to 4 2nd and 3rd column will keep the same pattern. Cabinet Shelf Location 1001 1 1 1001 1 2 1001 1 3 1001 1 4 1001 2 1 1001 2 2 1001 2 3 1001 2 4 1001 3 1 1001 3 2 1001 3 3 1001 3 4 1001 4 1 1001 4 2 1001 4 3 1001 4 4 1002 1 1 1002 1 2 1002 1 3 1002 1 4 1002 2 1 1002 2 2 1002 2...

Unhandled database exception: A get/change operation on table 'Bat
During Edit Checks, we get the following message: Unhandled database exception: A get/change operation on table 'Batch_Headers' could not find a record. Then we get this message: This transaction was recovered during normal processing. You may continue processing this transaction. Any suggestions? You might want to run Check Links on payables. Before doing this, make sure you have a backup of your dynamics and company databases. Brenner -- www.KlenzmanConsulting.com "GPI" wrote: > During Edit Checks, we get the following message: > > Unhandled databas...

Grand Substraction instead of Grand Total in pivot table
Hello, I created a pivot table in Excel with Visual Report 2007. I'm using an Outline Code with 2 leaves : Receipts and Expenses. The pivot table created automatically a Grand Total so that $1000 Receipts and $800 Expenses => Grand Total = $1800. My accountant says $200. Generally, it's possible to create calculated fields in the Excel pivot tables. But in the pivot table created by the Visual Reprot, all the Calculated Field options are greyed out. Why ? Thanks for any help I don't know what visual report is, but suspect that it would be more a question for them not Excel a...

if statement with dates #2
I would like to have a statement like: IF(a1>0,1/1/2004) with 1/1/2004 beint a date. EXCEL is interpreting 1/1/2004 as an arithmetic statement and giving me the result of the arithemtic. I could put in the numeric offste for the date and write IF(a1>0,37987), formatting the cell as a date, but this seems awkward. What else can I do? TIA, Sanford ...

How do I rotate a grouped object in Excel?
I have grouped some objects in Excel that I need to rotate. I can't activate a rotate handle for the grouped objects. ...

Change the format of my footer {date} option?
I would like to show the date in my footer as January 25, 2005 instead of the default 01/25/05 Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = Format(Date, "mmmm dd, yyyy") End With End Sub put this in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "Tori" <Tori@discussions.microsoft.com> wrote in message news:817363F1-32AB-4528-B1D6-304F8E712EA8@microsoft.com... > I would like to show the date in my footer as January 25, 2005 instead of the &g...

sorting activities for each lead/contact by actual end dates
When I view the activities associated with each lead and contacts, the actual end date are not shown. Does anyone know how I can dispaly this field? The activities list in workplace does show the actual end dates but we'd like to see that when we access the list through indivial contact/lead record. Thanks, Victoria Hi Victoria, There is no good out of the box way to do this in MSCRM today. We're working hard to fix this for v2.0. Many of our customers have worked around this issue by registering for a post call out on the activity creation, and then having a custom object wh...