Help calculating New (Incoming) items and monthly Backlog.

Pat Muirragui

New Member
Would appreciate your help on this. I have attempted to create a Pivot table to report the number of New Customer tickets and the Backlog (or open tickets) at the end of each month. I am operating on the following data (as an example):

Ticket# Date_Open Date Closed Status Customer Priority
0001 02/01/07 02/06/07 Closed A 1
0002 02/02/07 02/10/07 Closed B 1
0003 02/03/07 05/03/07 Closed C 2
0005 02/05/07 05/05/07 Closed A 3
0006 02/10/07 06/02/07 Closed C 2
0007 02/15/07 Open A 2
0008 03/05/07 Open B 1
0009 03/15/07 05/06/07 Closed D 1
0010 05/10/07 06/05/07 Closed A 1

For this data, the results I am looking for for New and Backlog tickets would be as follows, at the end of each month:
Note: I can get the New tickets, that is easy, the backlog is the challenge.

Month New Tickets Backlog
February 6 4
March 2 6
April 0 6
May 1 4
June 0 2

The problem I am running into is that the month the ticket is created and resolved may two different time frames, so I cannot operate on the results of each with calculated fields. e.g., say Ticket # 0003 was opened in February, .. but closed in May.

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Pat Muirragui

New Member
Was hoping for a quick answer, ... but no luck. I was able to find a solution, .. maybe not the best, but it works. It involves creating a pivot table with ResolvedDate on the Rows, Newly created on the Colums, and Count of Newly Created as the data. It creates a table that one can use to derive (through another table), the new tickets for each month (which is the row total, and the backlog) ... Would like to hear your ideas, ..

Pat

Replies
1
Views
93
Replies
17
Views
400
Replies
14
Views
200
Replies
5
Views
415
Replies
1
Views
729

1,181,657
Messages
5,931,261
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back