Overlapping and Missing Dates in a list of Contract

Question

I am auditing thousands of contracts per site location. We found two main problems, there were contracts renewed for one site that is overlapping and apparently there were months in between which doesn't have a valid contract. Overlapping is not allowed because services should be made once per month only, while missing dates or gaps also not allowed because the services must be continuous from the first contract. Below is sample information. Imagine I have this data in thousand, is there a formula or function available that could trigger these issues?

Answer 1

try:

=INDEX(LAMBDA(aa, bb, IF(""=TRIM(FLATTEN(QUERY(TRANSPOSE(SUBSTITUTE(LAMBDA(x, y, 
 XLOOKUP(x, INDEX(y,,1), INDEX(y,,2),,,1)&XLOOKUP(x, INDEX(y,,1), INDEX(y,,2),,,-1))
 (LAMBDA(a, b, IF(DAYS(b, a)+1<=SEQUENCE(1, MAX(DAYS(b, a))+1, 0),, 
 a+SEQUENCE(1, MAX(DAYS(b, a))+1, 0)))(aa, bb),
 LAMBDA(a, b, SPLIT(FLATTEN(IF(DAYS(b, a)+1<=SEQUENCE(1, MAX(DAYS(b, a))+1, 0),, 
 a+SEQUENCE(1, MAX(DAYS(b, a))+1, 0)&"×"&ROW(a))), "×"))(aa, bb)), ROW(aa), )),,9^9))),,"overlap"))
 (A8:INDEX(A:A, MAX((""<>A:A)*ROW(A:A))), B8:INDEX(B:B, MAX((""<>B:B)*ROW(B:B)))))