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

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

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


"MissSara" wrote in message
...
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

Hi,

this is an example that returns the number of days in the intersection
of two periods.

=MAX(MIN(EndDate1,EndDate2)-MAX(StartDate1,StartDate2)+1,0)

(assuming StartDate1<=EndDate1 and StartDate2<=EndDate2 are true.
and the period includes the start date and the end date.)

--
HTH,

okaizawa

MissSara wrote:
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

On Mon, 5 Sep 2005 10:26:29 +0100, "MissSara"
wrote:

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


In VBA one solution is a simple looping procedure e.g. below

This assumes A1 is named "Date1"
A2 is named "Date2"
A1 is the start date of the first period
B1 is the end date of the first period
A2 is the start date of the second period
B2 is the end date of the second period
A4 contains the text "Overlap Dates"

All dates are standard Excel date 'numbers'


Sub Overlapdates()
Dim date1 As Date, x As Double, y As Double
Dim date2 As Date

x = Range("date2").Cells(1, 2) - Range("date2").Cells(1, 1)

For y = 0 To x
If Range("date2") + y = Range("date1") And _
Range("date2") + y <= Range("date1").Cells(1, 2) Then
Range("a65536").End(xlUp).Offset(1, 0) = Range("date2") + y
End If
Next

End Sub

HTH



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Fantastic. Thank you very much.

Sara

"KL" wrote in message
...
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


"MissSara" wrote in message
...
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

Hi Sara,

The method proposed by Okaizawa looks much more efficient than mine. Just do
something like:

=MAX(MIN(EndDate1,EndDate2)-MAX(StartDate1,StartDate2)+1,0)0

Regards,
KL


"MissSara" wrote in message
...
Fantastic. Thank you very much.

Sara

"KL" wrote in message
...
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


"MissSara" wrote in message
...
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

Here's one if you need anymore

=AND(OR(IF(Start2<End1;"True";"False");IF(Start2=E nd1;"True";"False"));OR(IF(Start1<End2;"True";"Fal se");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.

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));O R(IF(Start1<End2;1;0);IF(Start1=End2;0;0)))

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


"Ivo" wrote in message
...
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));O R(IF(Start1<End2;1;0);IF(Start1=End2;0;0)))

"KL" wrote...
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

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 .

"Harlan Grove" wrote:

"KL" wrote...
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