+ Reply to Thread
Results 1 to 4 of 4

Pivot Table - 2 columns of dates, count per week

  1. #1
    Registered User
    Join Date
    08-03-2012
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    16

    Question Pivot Table - 2 columns of dates, count per week

    Hello, I'm looking for a way to organize this kind of data:
    Date Open Date Closed
    Issue 1 2012-01-01 2012-01-06
    Issue 2 2012-01-05 2012-01-08
    Issue 3 2012-01-10 2012-01-18
    Issue 4 2012-01-14 2012-01-18
    Issue 5 2012-01-15 2012-01-16

    Into a pivot table... that is grouped by week, and has two counts for each week: # of issues opened that week, and # of issues closes that week:
    Date # opened # closed
    Jan1-7 2 1
    Jan8-14 2 1
    Jan15-21 1 3

    The problem I am having is getting these sorted by weeks, without using either Date Open or Date Closed as the pivot table row labels. If I use either of those (DateO, I would get something like this:
    Row Labels Count of Date Open Count of Date Closed
    2012-01-01 - 2012-01-07 2 2
    2012-01-08 - 2012-01-14 2 2
    2012-01-15 - 2012-01-16 1 1

    Thanks.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Pivot Table - 2 columns of dates, count per week

    Hi ashakespeare,

    Please post a sample workbook with some open and closed issues in a month.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    08-03-2012
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Pivot Table - 2 columns of dates, count per week

    Here is some demo data, with the desired result as well.

    Thanksdemo_data.xlsx

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Pivot Table - 2 columns of dates, count per week

    Hi ashakespeare,

    See the attached file and let me know if this helps you.. thanks.

    demo_data.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ 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