+ Reply to Thread
Results 1 to 4 of 4

Grouping Dates into Monthly Totals - Pivot Table.

  1. #1
    Forum Contributor
    Join Date
    05-05-2006
    Posts
    143

    Grouping Dates into Monthly Totals - Pivot Table.

    I have read up on Pivot tables, and you are supposed to be able to group dates into monthly totals in a Pivot Table, or Weeks, etc.

    However as my list is daily I am following 'what I believe to be the set process of clicking on a date, selecting Grou & Show Detail to no avail.

    Atm I am getting the error message.

    "Cannot Group That Selection"

    Any ideas/workarounds?

    the date colum is formatted as Date.. \0:

    Ty in advance.
    Sp.

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Sam if you give us more detail, I am sure there is a work round. I use excel 97 so there may be more advanced features in 2003. By doing the table as individual dates this can be grouped into months, quarters or Years. If you want weeks, what day does the week start! for this reason weeks does not appear in the grouping options. A week can start on Saturday, Sunday or Monday depending what you are doing, hence it is not a default option

    but the dates are dates and not text I presume

    Tell us what you want the report grouped by, days, months, weeks etc and the other fields. Eg what does the table you imagine look like?

    eg
    Person1 Person2 Person3
    week1
    week2
    week3

    And the values are total sales

    Also are the items on the edge of the table fixed or known, eg if the people in my example are actually companies you trade with, the list could change daily and if the table was not created using a pivot table this could be problematical.



    Regards

    Dav
    Last edited by Dav; 10-11-2006 at 08:16 AM.

  3. #3
    Forum Contributor
    Join Date
    05-05-2006
    Posts
    143
    Basically pivot table is like this.


    Date --------------------Customers
    **/**/**----------------#of Customer for each day.
    From 05 to present.


    I have worked out the error,,, if there are any days which 0 customer on, they do no appear in the pivot table, and it wont group correctly,,

    Can you get dates that are not represented to appear nevertheless?

  4. #4
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    If your data is in columns A & B dates are in A and customers in B

    if you put the dates you want to appear in a range starting c1, you can drag it as long as you want down and the day should increment by one each time

    in d1 put =countif(A:A,d1) and copy it down

    assuming there is only one customer on each row

    Regards

    Dav

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1