#### grouping dates in pivot table

```I have an outstanding checklist which contains two accounts (account 2110
and 2189) I have created a pivot table using the date of the check on the
left, the account on top and the amounts as the data. Example

Account    2110    2189
Date
7/3/04                    105    176
7/6/04                    116    218
8/3/04                    786    197
8/7/04                    214    767

I am trying to group the dates so it will read total amount of outstanding
checks for the month. Example:

Account    2110    2189
Date
July '04                         221        394
Aug 04                        1000        964

When I highlight the word "Date" to group by month, I get a message that
says "Cannot Group this data"

What is the fastest way I can group by month?

```
 0
cmaronski (2)
5/21/2005 4:16:22 PM
excel 39879 articles. 2 followers.

2 Replies
451 Views

Similar Articles

[PageSpeed] 17

```Are your dates really dates?  They're not just text typed into the cell to look
like dates.  (You can check with a formula:

=isnumber(a2)
and copy down
If all of them returns TRUE, then they are dates.

If you have empty cells in your date range, then this would be a problem, too.

If you don't have gaps in your range, you could use just one formula:
=counta(a2:a999)=count(a2:a999)

If that returns True, then they are dates.

Charles Maronski wrote:
>
> I have an outstanding checklist which contains two accounts (account 2110
> and 2189) I have created a pivot table using the date of the check on the
> left, the account on top and the amounts as the data. Example
>
>             Account    2110    2189
> Date
> 7/3/04                    105    176
> 7/6/04                    116    218
> 8/3/04                    786    197
> 8/7/04                    214    767
>
> I am trying to group the dates so it will read total amount of outstanding
> checks for the month. Example:
>
>                        Account    2110    2189
>    Date
>     July '04                         221        394
> Aug 04                        1000        964
>
> When I highlight the word "Date" to group by month, I get a message that
> says "Cannot Group this data"
>
> What is the fastest way I can group by month?

--

Dave Peterson
```
 0
ec357201 (5290)
5/21/2005 5:08:03 PM
```Dave,

Thank you very much. I had empty cells in the date range. I was highlighting
the entire columns which gave me the empty cells. When I highlighted just
the data I needed, I was able to group by the month. Thanks again for the
help.

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:428F6AF3.9C7969A0@netscapeXSPAM.com...
> Are your dates really dates?  They're not just text typed into the cell to
> look
> like dates.  (You can check with a formula:
>
> =isnumber(a2)
> and copy down
> If all of them returns TRUE, then they are dates.
>
> If you have empty cells in your date range, then this would be a problem,
> too.
>
> If you don't have gaps in your range, you could use just one formula:
> =counta(a2:a999)=count(a2:a999)
> (adjust the range to match)
>
> If that returns True, then they are dates.
>
>
>
> Charles Maronski wrote:
>>
>> I have an outstanding checklist which contains two accounts (account 2110
>> and 2189) I have created a pivot table using the date of the check on the
>> left, the account on top and the amounts as the data. Example
>>
>>             Account    2110    2189
>> Date
>> 7/3/04                    105    176
>> 7/6/04                    116    218
>> 8/3/04                    786    197
>> 8/7/04                    214    767
>>
>> I am trying to group the dates so it will read total amount of
>> outstanding
>> checks for the month. Example:
>>
>>                        Account    2110    2189
>>    Date
>>     July '04                         221        394
>> Aug 04                        1000        964
>>
>> When I highlight the word "Date" to group by month, I get a message that
>> says "Cannot Group this data"
>>
>> What is the fastest way I can group by month?
>
> --
>
> Dave Peterson

```
 0
cmaronski (2)
5/22/2005 12:20:26 AM

Similar Artilces:

Grouping data
I am trying to group data for a specific event and have not been successful, so I am hoping someone can help. ID Date Value 174 12/3/2007 102 174 12/3/2007 909 174 12/11/2007 405 174 12/11/2007 701 206 12/6/2007 1001 173 12/3/2007 500 I want to group by ID, the MAX Date for that ID and the Max Value for the ID & Max Date The desired result would be: ID Date Value 174 12/11/2007 701 206 12/6/...

Group Footer at Bottom of Last Page of Group
I would like for a group footer to appear at the bottom of the last page of a group -- either hide the page footer on pages if it is not the last page of the group or have the group footer print at the bottom of the page. I have used the instructions to reset the page number and the total page count for each group. (http://support.microsoft.com/kb/841779/en-us) I was hoping to be able to set it where if the page equals the total pages of the group, it will make the page footer visible. For some reason, if there is more than one page, it doesn't work properly. I have also used the i...

query based distribution group
Hello all running Exchange 2007 sp2, I need to modify an existing query based DL so it finds mailboxes and if the account has two different email address stamped on it. For example if the mailbox has test1.com and test2.com and it's a mailbox then make it a member of the query based DL Thanks for any help If it has those two e-mail addresses? That should return just one recipient. Or do you mean a recipient with any two proxy addresses? I don't believe that's possible with LDAP. Or do you mean a mailbox-enabled user with proxy addresses in two domai...

Reference a Date and Time in Query
Hello, I am trying to use a query to filter the table results by Date and Time. The table data is in the format: mm/dd/yyyy HH:MM:SS *M I already have a query that selects the data between two dates. But I also want to be able to select two dates and two times. Such as Between 3/15/2007 9:00 AM and 3/15/2007 10:00 AM, or use two different dates and times, etc. I have gotten close by using: Between ([Forms]![RC_WithTime]![Calendar1]+TimeSerial(9,0,0)) And ([Forms]![RC_WithTime]![Calendar2]+TimeSerial(10,0,0)) but as soon as I try to convert the TimeSerial to link to forms it says that the...

text as value in pivot table
I need to show some references in de form of A11_A11_1a in a table, and with the order given by each section of the references. I tried using pivot tables and sorting with the first and second sections. But the pivot table will show the count of occurring matches. How can I show the actual reference? Regards ...

last sale date
HI All I am looking for a table that would have the date that an inventory item was sold on. I browsed sop and inventory tables. I found last purchased date on IV QTY Master IV00102, but I cant find one for sales. Anyone know if there is such a field and what table it may be on? -- Ian There does not appear to be such a date. "Ian Richardson" <IanRichardson@discussions.microsoft.com> wrote in message news:536A4CEF-789B-465B-A5C8-F0FF4722FEAB@microsoft.com... > HI All > > I am looking for a table that would have the date that an inventory item > was > s...

Creating Universal Groups with Distribution Group members?
Hello all. We have a Windows 2003 Active Directory with Exchange 2003, and I don't really want to duplicate work, but I think I may have to (ugh!). Here is the deal. I need to create an All Managers Universal Group with every group that has managers in it. So, what I tried doing, was I created a Universal Group and added the _All Managers DL into it -- but that didn't seem to work, according to my boss who asked me to create this UG for him. So, my question is, am I not allowed to add DL members to a Universal Security Group? And if now, is there any way that I can take...

Pivot Table Repeats Names in multiple rows
I have data on hours worked for about 70 employees (Names are rows) for the last year (Months are columns). For about 15 of those names, the Pivot Table is not consolidating all the data for that employee. Rather, it repeats the name for 2 or more rows as if those names were spelled in various ways. The hours data for those affected employees are also broken out among the various rows. The source data originally had this problem, but I fixed all the misspellings. By filtering the names field in my source data, I have confirmed that there is now only one version of each name. Why c...

Distribution Group list
I have a problem with the distribution group list. Here it goes: I removed and deleted the account from this group list but whenever I send a task, it always tries to reach this deleted account and give me a undeliverable error message. Please help. Thanks Restart Exchange services. Linh wrote: > I have a problem with the distribution group list. Here > it goes: I removed and deleted the account from this > group list but whenever I send a task, it always tries to > reach this deleted account and give me a undeliverable > error message. Please help. Thanks ...

setup formula to calculate random dates
Hi all, I am trying to setup a formula to calculate employees terminated each months. I hate doing this and it sucks. I have a employee spreadsheets and it contains all the users (hire date, termination date). the data is enter in different columns. At the end of the month, I need to know how many employees are teminated. I have another column by months (Jan, Feb, .... December) I need to calculate how many people were layed off each month. Thanks for all your help. Try Sorting by the Month Column and then using Data | Subtotals to Count the Entries in the Terminated Column every time the...

maximum rows limitation in an access 97 table
we have an access97 db which has 32405 rows, is there such a limit of 32700 rows in access 97 and if there is, can this be rosolved by upgrading to Access 2000 or higher. > is there such > a limit of 32700 rows in access 97 No, the limit is that the database cannot be larger than 1GB. Check out other specifications in Access Help, using "specifications" as a search criterin in the Search Wizard. -- Cheryl Fischer Law/Sys Associates Houston, TX "GM" <anonymous@discussions.microsoft.com> wrote in message news:07b501c3aadf\$4d6d6630\$a001280a@phx.gbl... >...

Data, Group got error message Cannot shift object off sheet
I created a command grouping several columns together by going to Data, Group. When I go to click on the "-" sign to group. It gives me an error message;"Cannot shift object off sheet". Why? and how can I fix it. Maybe you have some objects that get rearranged when you hide the columns using your grouping symbols. This may have some suggestions that work for you. It's kind of a similar problem. XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769 Remember to look for comments and merged ...

dates change when I copy and paste
I have a report that has columns for Day of week and a column for th date(10/15/04) but when I copy and paste to another excel worksheet th date changes from ex: 10/15/04 to 10/13/08. Any ideas why this woul happen. It's a pretty lengthy report and I hate to always have t change each date. Help. : -- Nesi ----------------------------------------------------------------------- Nesia's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1534 View this thread: http://www.excelforum.com/showthread.php?threadid=26965 Hi both workbooks have different date setting (...

Outlook 2007 Export w/ Date
We have a need to print a list of e-mails from Outlook 2007, so we can make comments on each of them. The export seemed to be a perfect fit for this, because we could bring it into Excel, and type our notes. However the export does not include the date field. That's crazy. That's one of the main pieces. Is there any way at all to export this data and include the dates? Thanks, Jesse http://www.outlook-tips.net/howto/copy_data.htm - if you need the message body, add the message field to the view and copy and paste. Note that it works best if don't need the body - or at...

Dates and Math
I have two columns listing date and time in the MM/DD/YYYY TT:TT:TT format. I would like to subtract one from the other to get the time it takes to do X. Any ideas? Hi try =A2-A1 and format with the custom format [hh]:mm -- Regards Frank Kabel Frankfurt, Germany "jimlib" <j1ml1b@hotmail.com> schrieb im Newsbeitrag news:d5b18b0e.0411191423.1ee756dd@posting.google.com... > I have two columns listing date and time in the MM/DD/YYYY TT:TT:TT > format. I would like to subtract one from the other to get the time it > takes to do X. Any ideas? ...

Local Data Groups
Hi, Does anyone know if its possible to create local data groups for offline clients and somehow push the groups out to all users, rather than going to each outlook laptop client? Jo Savidge You can deploy the SFO client using group policies. When the user next logs onto the domain the SFO client will be installed ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm http://www.crowechizek.com/microsoft "Jo Savidge" <JoSavidge@discussions.microsoft.com> wrote in message news:CAE65DA4-C2E3-4F50-A78B-39C98940A129@microsoft.c...

Date format 04-19-07
Good afternoon: I have a field "ship date" in a table that is populated as follow "05JAN2006" (DDMMMYYYY) and this field is a text field. I would like to turn this field into a date field where I could make calculations and REALLY have a date information. I would like to add a column to the table and use an update query to transfor the ship date into a real date... What is the function you would recommend using to do so? Is there a better way than to add a column, upodate and then remove one column? Thanks Michel Khennafi wrote: > Good afternoon: > > I ...

Printed calendar includes more dates than requested in page setup
I am using Wndows 2007 and am trying to print a 1 month calendar in Outlook with weekdays only that includes specific dates, but I'm getting more dates than I want. ie I specified date of 1/4/10 through 1/29/10. Printed calendar includes the weeks of 12/28/09 and 1/31/10. Also, some days (not events) are shaded, but I can't f determine why. when you print a monthly calendar, its going to include the full month - if the first or last of the month is in a week with another month, those dates will be included in the printout. 1/31 should not be included since itâ€™s a su...

I have a table that gets generated every month, and we use as a source of data for other databases. We link this, but I was wondering if there is a way to make that linked table read only. Any ideas? Thanks so much, Chris M. hi Chris, mcescher wrote: > I have a table that gets generated every month, and we use as a source > of data for other databases. We link this, but I was wondering if > there is a way to make that linked table read only. Either make the back-end read-only or use a pass-through query instead of a linked table. mfG --> stefan <-- ...

Last login date by a work resource
Hi, Can some please help me to get to know the latest date when a work resource logged in project server 2007? GP -- In PWA, click Server Settings - Manage Users. Look in the Last Connect column to see the date each user logged into the Project Server 2007 system. Beyond this, you can also export this data to Excel for further analysis by clicking Actions - Export to Excel. Hope this helps. -- Dale A. Howard [MVP] VP of Educational Services msProjectExperts http://www.msprojectexperts.com http://www.projectserverexperts.com "We write the books on Project Server&q...

Summing dates
I have table like this Date 1 Date2 Diff Date2-Date1 19.12.05 10.01.06 0Y 0M 22D 14.01.05 26.02.05 0Y 1M 13D Which formula to use to sum dates in column 3. For easyer calculations assume that month have 30 days. Tahnx See: http://www.excelforum.com/showthread.php?t=558873 -- Vito ------------------------------------------------------------------------ Vito's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29182 View this thread: http://www.excelforum.com/showthread.php?threadid=558872 Just subtract =B2-A2 -- HTH Bob Phi...

graphing by group
I have 10 individuals in 5 groups. I would like to graph the individuals but keeping them within their groups. When I try to graph it ends up as 10 groups. I would also like to make each group of individuals a different colour. Thanks, Tim Hi Tim In Step 2 of Chart Wizard make sure you have Series by Rows or Series by Columns correctly selected. If you groups are in different columns us Series by Columns other wise use Series by Rows -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Tim Pasma" <anonymous@discussions.microsoft.com> wrote in m...

FAQ to find difference of two dates expressed in days
can someone direct me to the FAQ where I could probably find out how to determine the difference between two dates expressed in days thank you portella@optonline.net ...

SupplierList Table doesn't get filled up autom. when adding an ite
When adding an item, the item is not getting added automatically in the SQL SupplierList table although it used to at first. Is there any functionnality in RMS as to enabling or disabling this feature? After adding a product, if I go in the item properties under the supplier tab, I see it's empty although I selected a supplier when adding the item. ...

y2k date issue
I have an old database file in DBF format that I use Microsoft Access to display. The dates in the DBF file only has 2 digits, so when it's opened under Access, some dates are displayed as "19xx", while other dates just as "xx". It creates quite a problem when I sort or search by the dates. I do have a simple way to figure out whether the year "xx" is 20xx or 19xx, so I was wondering if there is a way to add a small macro for the date fields, such that when this DBF file is opened under Access, proper conversion of the year would take place automatically. On...