Open incidents by month end

[Dwayne20]

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.

So I would need to work out the age of each incident that in scope by either subtracting the closed date by open date or the month end date by the open date, add them up and then divide by number of incidents in scope

Any ideas?

 

[GraH]

Try with countifs.

 

[GraH]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Case	Open	Closed			Status on: 31/05/2022	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	1	1/01/2022	23/05/2022			Cases In	2	1	0	0	0	0	0	0	0	0	0	0
3	2	1/01/2022	15/06/2022			Cases Out	0	0	0	0	1	1	0	0	0	0	0	0
4	3	10/02/2022				Backlog	2	3	3	3	2
Sheet1

Cell Formulas
Range	Formula
F1	F1	=TODAY()
G2:R2	G2	=SUMPRODUCT((MONTH(Cases[[Open]:[Open]])=MONTH(G$1))*(YEAR(Cases[[Open]:[Open]])=YEAR(G$1)))
G3:R3	G3	=SUMPRODUCT((MONTH(Cases[[Closed]:[Closed]])=MONTH(G$1))*(YEAR(Cases[[Closed]:[Closed]])=YEAR(G$1)))
G4:R4	G4	=IF(G$1>$F$1,"",SUM($G2:G2)-SUM($G3:G3))