1

I am looking for a solution on either Google sheets or app script to check for overlapping dates for the same account. There will be multiple accounts and the dates won't be in any particular order. Here is an example below. I am trying to achieve the right column "check" with some formula or automation. Any suggestions would be greatly appreciated.

Start Date End Date Account No. Check
2023-01-01 2023-01-02 123 ERROR
2023-01-02 2023-01-05 123 ERROR
2023-02-25 2023-02-27 456 OK
2023-01-11 2023-01-12 456 OK
2023-01-01 2023-01-15 789 ERROR
2023-01-04 2023-01-07 789 ERROR
2023-01-01 2023-01-10 012 OK
2023-01-15 2023-01-20 012 OK

I also found some similar past questions, but they don't have the "for the same account" component and/or requires some sort of chronological order, which my sheet will not have.

How to calculate the overlap between some Google Sheet time frames?

How to check if any of the time ranges overlap with each other in Google Sheets

Ken White
  • 123,280
  • 14
  • 225
  • 444
houjicha
  • 13
  • 4
  • Note that the two references you give _Do not check for overlaps globally_ even when the data is sorted. Please see my answer for further comment. – Tom Sharpe Jan 17 '23 at 22:21

2 Answers2

0

(1) Here is one way, considering each case which could result in an overlap separately:

=ArrayFormula(if(A2:A="",,
if((countifs(A2:A,"<="&A2:A,B2:B,">="&A2:A,C2:C,C2:C,row(A2:A),"<>"&row(A2:A))
+countifs(A2:A,"<="&B2:B,B2:B,">="&B2:B,C2:C,C2:C,row(A2:A),"<>"&row(A2:A))
+countifs(A2:A,">="&A2:A,B2:B,"<="&B2:B,C2:C,C2:C,row(A2:A),"<>"&row(A2:A))
)>0,"ERROR","OK")
)
)

enter image description here

(2) Here is the method using the Overlap formula

min(end1,end2)-max(start1,start2)+1

which results in

=ArrayFormula(if(byrow(A2:index(C:C,counta(A:A)),lambda(r,sum(text(if(index(r,2)<B2:B,index(r,2),B2:B)-if(index(r,1)>A2:A,index(r,1),A2:A)+1,"0;\0;\0")*(C2:C=index(r,3))*(row(A2:A)<>row(r)))))>0,"ERROR","OK"))

enter image description here

(3) Most efficient is to use the original method of comparing previous and next dates, but then you need to sort and sort back like this:

=lambda(data,sort(map(sequence(rows(data)),lambda(c,if(if(c=1,0,(index(data,c-1,2)>=index(data,c,1))*(index(data,c-1,3)=index(data,c,3)))+if(c=rows(data),0,(index(data,c+1,1)<=index(data,c,2))*(index(data,c+1,3)=index(data,c,3)))>0,"ERROR","OK"))),index(data,0,4),1))(SORT(filter({A2:C,row(A2:A)},A2:A<>""),3,1,1,1))

HOWEVER, this only checks for local overlaps. not globally. You can see what I mean if you change the dataset slightly:

enter image description here

Clearly the first and third pair of dates have an overlap but G4 contains "OK". This is because each pair of dates is only checked against the adjacent pairs of dates. This also applies to the original reference cited by OP - here's an example where it would give a similar result:

enter image description here

The formula posted by @The God of Biscuits gives the correct (global) result :-)

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

Another approach (to be entered in D2):

=arrayformula(lambda(last_row,
lambda(acc_no,start_date,end_date,
if(isnumber(match(acc_no,unique(query(query(split(flatten(acc_no&"|"&split(map(start_date,end_date,lambda(start_date,end_date,join("|",sequence(1,end_date-(start_date-1),start_date)))),"|")),"|"),"select Col1,count(Col2) where Col2 is not null group by Col1,Col2",0),"select Col1 where Col2>1",1)),0)),"ERROR","OK"))(
C2:index(C2:C,last_row),A2:index(A2:A,last_row),B2:index(B2:B,last_row)))(
counta(A2:A)))

Briefly, we are creating a sequence of dateserial numbers between the start & end dates for each row, doing some string manipulation to turn it into a table of account number against each date, then QUERYing it to get each account number which has dateserials with count>1 (i.e. overlaps), using UNIQUE to get the distinct list of those account numbers, then finally matching this list against the original list of account numbers to give the ERROR/OK output.

The God of Biscuits
  • 2,029
  • 2
  • 3
  • 10