• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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),AND(E2>=B2,E2<=C2)),"Yes","No")

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
0
jbakestull
Asked:
jbakestull
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
ajcheung78Commented:
Looks like you have times on the dates so that is why your formula is not working when it seems as though the dates are equal.  Did you actually intend to not take into account times?

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.
0
 
StephenJRCommented:
What should happen if (1) the dates are equal and (2) the exit date is blank?
0
 
jbakestullAuthor Commented:

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.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
barry houdiniCommented:
Does this formula do it for you?

=IF(OR(INT(D2)>IF(C2="",10^7,INT(C2)),IF(E2="",10^7,INT(E2))<INT(B2)),"No","Yes")

regards, barry
0
 
jbakestullAuthor Commented:
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.
0
 
ajcheung78Commented:
Please see the attached worksheet:

=IF(OR(AND(INT(D2)>=INT(B2),INT(D2)<=INT(IF(ISBLANK(C2),E2,C2))),AND(INT(E2)>=INT(B2),INT(E2)<=INT(C2))),"Yes","No")
Overlapping-Dates--Updated.xlsx
0
 
jbakestullAuthor Commented:
barryhoudini:


Does this formula do it for you?

=IF(OR(INT(D2)>IF(C2="",10^7,INT(C2)),IF(E2="",10^7,INT(E2))<INT(B2)),"No","Yes")

It works.. wow, thanks..

I've never seen this (10^7) is formula before,, what does it mean?
0
 
barry houdiniCommented:
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
0
 
jbakestullAuthor Commented:
ajcheung78: your formula works too,

To make it fair, I'm going to split the points between you and Barryhoudini,

thanks for your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now