Month end age of incidents

[Dwayne120]

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?

[#2]

@Dwayne120 

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.

[#3]

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?

[#4]

@Dwayne120 

See the attached sample workbook. The formulas are horrible.