Calculating the unique days across overlapping date ranges

[Strexxin]

Good day,

I am having some problems writing out a formula to calculate the number of unique days a system has been down if there are 2 or more tickets raised against it at the same time. In the below example data the result for System 1 being down would be 22 days as that is the difference between the start date on the first ticket and the end date on the second ticket. However every formula I write calculates each ticket individually and gives me a downtime of 32 days.

Ticket Number	Serial Number	System Type	Ticket Start Date	Resolution Date
1234	SN0001	System 1	09/01/2025	28/01/2025
5678	SN0001	System 1	18/01/2025	31/01/2025

My current attempt is
=LET(_min, MINIFS($E$2:E5, $E$2:E5, "<"&F5, $F$2:F5, ">"&MIN($F$2:F5), $D$2:D5, $K$1),
_max, MAXIFS($F$2:F5, $F$2:F5, MAXIFS($F$2:F5, $D$2:D5, $K$1)),
_max - _min)


I have managed to tie myself in knots so any help on this would be appreciated

 

[Cubist]

I am still getting 0 from Eric' formula and I am getting 1 from Cubist' formula

I get 1's with my formula when the dates are text. Test your date columns with =ISNUMBER().See column L.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Ticket Number	Ticket Status	Ticket Owner	Serial Number	System Type	System Location	Ticket Start Date	Ticket Resolution Date	System Install Date	Age of System at Time of Ticket Raised (Days)	Ticket Resolution Time (Days)	ISNUMBER		System Type	Unique Days		Serial Number	Unique Days
2	10208	Closed		GM0683	RADiant Aura 50Kv		23/1/25	12/2/25	25/06/2024	212	20	FALSE		CIX3	1		2010-0505	1
3	10368	Open		GS105	CIX3		14/2/25		28/09/2021	1235		FALSE		Medical Software	1		2016-0755	1
4	10074	Open		gs099	CIX3		19/12/24		14/12/2020	1466		FALSE		RADiant 50Kv	1		2020-0879	1
5	10113	Closed		GM0379	X150		2/1/25	17/2/25	25/07/2014	3814	46	FALSE		RADiant 80Kv	1		GM0086	1
6	10235	Open		GM0517	RADiant 80Kv		27/1/25		16/05/2019	2083		FALSE		RADiant Aura 50Kv	1		GM0379	1
7	10242	Closed		GM0683	RADiant Aura 50Kv		28/1/25	17/2/25	25/06/2024	217	20	FALSE		SARRP	1		GM0450	1
8	10190	Open		2020-0879	SARRP		20/1/25					FALSE		X150	1		GM0469	1
9	10331	Open		GM0640	X200		10/2/25		20/06/2024	235		FALSE		X200	1		GM0517	1
10	9954	Open		GM0536	X300		12/2/24		27/08/2021	1193		FALSE		X300	1		gm0522	1
11	10287	Open		GM0564	X200		4/2/25		19/03/2021	1418		FALSE					GM0528	1
12	10049	Open		GM0450	Medical Software		16/12/24		15/09/2017	2649		FALSE					GM0536	1
13	10198	Open		gm0522			21/1/25		15/11/2019	1894		FALSE					GM0564	1
14	10199	Open		2016-0755	SARRP		21/1/25		29/08/2016	3067		FALSE					GM0640	1
15	10168	Closed		GM0528	RADiant 50Kv		15/1/25	18/2/25	22/06/2021	1303	34	FALSE					GM0663	1
16	10026	Closed		GM0528	RADiant 50Kv		10/12/24	18/2/25	22/06/2021	1267	70	FALSE					GM0683	1
17	9358	Closed		GM0469	X150		11/9/24	18/2/25	20/07/2018	2245	160	FALSE					gs099	1
18	10384	Open		2010-0505	SARRP		18/2/25					FALSE					GS105	1
19	9909	Closed		GM0663	X200		21/11/24	18/2/25	28/06/2024	146	89	FALSE					GS147	1
20	10069	Open		GS147	CIX3		18/12/24		00/01/1900	45644		FALSE
21	10193	Open		GM0469	X150		20/1/25		20/07/2018	2376		FALSE
22	10318	Open		GM0086	X150		6/2/25		38149	7397		FALSE
Sheet9

Cell Formulas
Range	Formula
G2:H22	G2	=IF(T2:U22="","",TEXT(T2:U22,"d/m/yy"))
L2:L22	L2	=ISNUMBER(G2:G22)
N2:N10	N2	=SORT(TOCOL(UNIQUE(E2:E22),1))
O2:O10	O2	=BYROW(MAXIFS(H2:H22,E2:E22,N2#)-MINIFS(G2:G22,E2:E22,N2#)+1,LAMBDA(x,MAX(0,x)))
Q2:Q19	Q2	=SORT(TOCOL(UNIQUE(D2:D22),1))
R2:R19	R2	=BYROW(MAXIFS(H2:H22,D2:D22,Q2#)-MINIFS(G2:G22,D2:D22,Q2#)+1,LAMBDA(x,MAX(0,x)))
Dynamic array formulas.

 

[Strexxin]

I want to thank both of you, the problem appeared to have lay with me. I was automating the input of this sheet from another system and that input was coming in as a strange format. Upon changing this format to date both solutions now work brilliantly.