Month end age of incidents

New Contributor



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:




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.