I NEED A FUNCTION TO FIND IF THERE IS AN INTERSECTION BETWEEN 2 DATE RANGES

[MissSara]

Hello, I need a function which allows me to enter the start and end dates
for two date-periods, then returns 'True' if there is any crossover
(intersection) between the two periods. In simple terms if there is any day
(or days) which appears in both periods.

Can I do this with worksheet functions? or do I need VBA? Very grateful for
any tips on how I code it.

Sara

 

[KL]

Hi,

There must be a more robust way of doing it, but you could try this ARRAY
formula (commit it by hitting Ctrl+Shift+Enter, not just Enter):

=OR(StartDate1+ROW(INDIRECT("1:"&EndDate1-StartDate1+1))=StartDate2+TRANSPOSE(ROW(INDIRECT("1:"&EndDate2-StartDate2+1))))

Regards,
KL

 

[Guest]

Here's one if you need anymore

=AND(OR(IF(Start2<End1;"True";"False");IF(Start2=End1;"True";"False"));OR(IF(Start1<End2;"True";"False");IF(Start1=End2;"True";"False")))

It looks weird but all it does is compare the end dates to the start dates
and if one of them is larger then they intersect.

 

[Guest]

Problem with the quotation marks so here it is with 1's and 0's instead of
true/false

=AND(OR(IF(Start2<End1;1;0);IF(Start2=End1;1;0));OR(IF(Start1<End2;1;0);IF(Start1=End2;0;0)))

 

[KL]

Hi Ivo,

Your formula can be further reduced to:

=AND(Start2<=End1,Start1<=End2)

BTW the logical values TRUE and FALSE are usually used without quotation
marks, otherwise the values returned are text strings.

Regards,
KL

 

[Harlan Grove]

Your formula can be further reduced to:

=AND(Start2<=End1,Start1<=End2)

....

Technically this wouldn't be overlap is either but not both conditions were
TRUE due to exact equality. Drop the equal signs

 

[Guest]

Actually you do need to check for <= instead of just < beacause if one of the
periods ends at the same date as the start of the other the < check would be
false .