Query: I have a range of dates, start date and end date in a single row in below format.
I want to check for overlap for in each row using formula .
Solution:
An overlap exists if any of end date is equal to or lesser than start date for other blocks or any of start date is equal to or greater than end date for other blocks.
We will compare each end date for criteria 1 & compare each start date criteria 2 as mentioned above using following formula.
=IF(SUMPRODUCT((INDEX((A2:F2)*(MOD(COLUMN(A2:F2),2)<>0),,)<=TRANSPOSE(INDEX((B2:G2)*(MOD(COLUMN(B2:G2),2)=0),,)))*(INDEX((B2:G2)*(MOD(COLUMN(B2:G2),2)=0),,)>=TRANSPOSE(INDEX((A2:F2)*(MOD(COLUMN(A2:F2),2)<>0),,))))>1,"Overlap","No Overlap")
Download sample file.
Post your feedback below & write your query to www.ExcelVbaLab.Com
Cheers!!
I want to check for overlap for in each row using formula .
Solution:
An overlap exists if any of end date is equal to or lesser than start date for other blocks or any of start date is equal to or greater than end date for other blocks.
We will compare each end date for criteria 1 & compare each start date criteria 2 as mentioned above using following formula.
=IF(SUMPRODUCT((INDEX((A2:F2)*(MOD(COLUMN(A2:F2),2)<>0),,)<=TRANSPOSE(INDEX((B2:G2)*(MOD(COLUMN(B2:G2),2)=0),,)))*(INDEX((B2:G2)*(MOD(COLUMN(B2:G2),2)=0),,)>=TRANSPOSE(INDEX((A2:F2)*(MOD(COLUMN(A2:F2),2)<>0),,))))>1,"Overlap","No Overlap")
Download sample file.
Post your feedback below & write your query to www.ExcelVbaLab.Com
Cheers!!
No comments:
Post a Comment