New Contributor

# 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

# Re: Month end age of incidents

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.

# Re: Month end age of incidents

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?

# Re: Month end age of incidents

See the attached sample workbook. The formulas are horrible.