Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 __________________________ |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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))) |
#9
|
|||
|
|||
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))) |
#10
|
|||
|
|||
"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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find the contents of a cell using the "ADDRESS" function. | Excel Worksheet Functions | |||
Help with the FIND function | Excel Worksheet Functions | |||
Worksheet Function - Find? | Excel Worksheet Functions | |||
find calculation function | Excel Worksheet Functions | |||
how to get the intersect cell data out using vba or function | Excel Worksheet Functions |