jbakestull
asked on
Overlapping two sets of dates
Morning all,
I'm not sure how to upload an image, but I was able to included an attachment.
I'm trying create a formula to identify if two sets of dates (Start and End) overlap another set of dates (Entry and Exit dates).
The Entry and Exit dates related to when a client enters into program. The start and end dates are related to clients income (starting and Ending Dates).
The objective is identify if start and end dates overlap with Entry and exit dates.
I've been using this formula: =IF(OR(AND(D2>=B2,D2<=C2),
Formula works correctly as along as the dates are not equal or if exit date is not blank.
I'm not sure how to modify formula correctly to capture dates that are equal (example client #1611) or when exit date is blank (example client #2473).
The only rule is that a client has to have an entry and start date.
Client Start Date End Date Entry Date Exit Date Overlap
902 12/23/2010 2/4/2011 1/26/11 1/26/11 Yes
1288 5/2/2011 6/16/2011 6/15/11 6/15/11 Yes
1602 1/18/2011 1/28/2011 1/18/11 1/18/11 No
1611 7/18/2011 7/19/11 7/19/11 No
1817 6/30/2011 7/22/2011 6/30/11 6/30/11 No
1818 7/7/2011 7/28/2011 7/7/11 7/7/11 No
2010 7/17/2011 7/29/2011 7/21/11 7/21/11 Yes
2124 8/17/2010 12/31/2011 6/8/11 6/8/11 Yes
2124 6/1/2011 6/8/11 6/8/11 No
2473 4/30/2011 6/17/2011 6/10/11 6/10/11 Yes
2473 6/1/2011 6/17/2011 6/10/11 6/10/11 Yes
Overlapping-Dates.xlsx
I'm not sure how to upload an image, but I was able to included an attachment.
I'm trying create a formula to identify if two sets of dates (Start and End) overlap another set of dates (Entry and Exit dates).
The Entry and Exit dates related to when a client enters into program. The start and end dates are related to clients income (starting and Ending Dates).
The objective is identify if start and end dates overlap with Entry and exit dates.
I've been using this formula: =IF(OR(AND(D2>=B2,D2<=C2),
Formula works correctly as along as the dates are not equal or if exit date is not blank.
I'm not sure how to modify formula correctly to capture dates that are equal (example client #1611) or when exit date is blank (example client #2473).
The only rule is that a client has to have an entry and start date.
Client Start Date End Date Entry Date Exit Date Overlap
902 12/23/2010 2/4/2011 1/26/11 1/26/11 Yes
1288 5/2/2011 6/16/2011 6/15/11 6/15/11 Yes
1602 1/18/2011 1/28/2011 1/18/11 1/18/11 No
1611 7/18/2011 7/19/11 7/19/11 No
1817 6/30/2011 7/22/2011 6/30/11 6/30/11 No
1818 7/7/2011 7/28/2011 7/7/11 7/7/11 No
2010 7/17/2011 7/29/2011 7/21/11 7/21/11 Yes
2124 8/17/2010 12/31/2011 6/8/11 6/8/11 Yes
2124 6/1/2011 6/8/11 6/8/11 No
2473 4/30/2011 6/17/2011 6/10/11 6/10/11 Yes
2473 6/1/2011 6/17/2011 6/10/11 6/10/11 Yes
Overlapping-Dates.xlsx
What should happen if (1) the dates are equal and (2) the exit date is blank?
ASKER
Thanks for replying.
I totally forgot about time stamps, I know how to remove time stamps using int().
If the end date is blank, we assume the it is all of eternity.
"I assume you want the overlap to say Yes even though you do not have an end date - is that a correct statement". = Yes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I thought I had all bases covered, sorry,
StephenJR: What should happen if (1) the dates are equal and (2) the exit date is blank?
If the start date is equal too or prior to the entry date that is acceptable.
If the exit date is blank that means that client is still in the program. If client is still in the program and income has a start and end date that also is acceptable.
StephenJR: What should happen if (1) the dates are equal and (2) the exit date is blank?
If the start date is equal too or prior to the entry date that is acceptable.
If the exit date is blank that means that client is still in the program. If client is still in the program and income has a start and end date that also is acceptable.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
barryhoudini:
Does this formula do it for you?
=IF(OR(INT(D2)>IF(C2="",10
It works.. wow, thanks..
I've never seen this (10^7) is formula before,, what does it mean?
Does this formula do it for you?
=IF(OR(INT(D2)>IF(C2="",10
It works.. wow, thanks..
I've never seen this (10^7) is formula before,, what does it mean?
10^7 is just a very large number, a 1 with 7 zeroes, that will be larger than any valid date in Excel....
regards, barry
regards, barry
ASKER
ajcheung78: your formula works too,
To make it fair, I'm going to split the points between you and Barryhoudini,
thanks for your help.
To make it fair, I'm going to split the points between you and Barryhoudini,
thanks for your help.
Also when the End Date is blank is it is assumed that the end date is all of eternity or what should be assumed. Based on your spreadsheet and comments above I assume you want the overlap to say Yes even though you do not have an end date - is that a correct statement.