Forum Discussion
Month end age of incidents
Hi.
I have some incident data that has incident number, date opened and date closed.
Case #1 was Opened January 1st and Closed on
May 23rd
Case #2 was Opened January 1st and Closed on June 15th
Case #3 was Opened on February 10th and is still open
If I were to report on how many cases were "open" at the end of the month from January to July, it would look like this
January - 2 Cases in Backlog (#1 and #2)
February - 3 Cases in Backlog (#1, #2 and #3)
March and April - 3 Cases in Backlog (#1, #2 and #3)
May - 2 Cases in Backlog (#2 and #3, because #1 closed before the end of May)
June and July - 1 Case in Backlog (#3 because #2 closed before the end of June)
For where the incident is not closed I want to be able to count from when the incident was open to that months reporting period. So in March for case 3 I would calculate I would calculate from the 10th of Feb to 31st March.
For each month I would need to work out the average number of days incidents remains open. So looking where a closed date does not exist or where the closed date was after the reporting month. Not really sure how to tackle this without splitting the data out by month which I want to avoid. Any ideas?
3 Replies
Like this:
The formula in G2 is:
=SUMPRODUCT(($B$2:$B$11<=F2)*(IF($C$2:$C$11="",DATE(9999,12,31),$C$2:$C$11)>=F2))
This can be filled down. You'll have to adjust the ranges, of course.
- Thank you for the reply, this counts the number of incidents which a good step but in the Jan example I would count the number of days for incidents still open that were notified before 2022-01-31 and either not closed yet or closer after this date. In this example my result would be 31 as it's two incidents open on 2022-01-01 so that would be 63/2. Does that make sense?
See the attached sample workbook. The formulas are horrible.
