SOLVED

Find if a date contained in one range is present in another range

%3CLINGO-SUB%20id%3D%22lingo-sub-2806492%22%20slang%3D%22en-US%22%3EFind%20if%20a%20date%20contained%20in%20one%20range%20is%20present%20in%20another%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806492%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20with%20a%20yes%2Fno%20type%20outcome%20for%20the%20attached%20problem.%3C%2FP%3E%3CP%3EI%20have%202%20date%20ranges%20and%20I%20want%20to%20know%20if%20any%20of%20the%20dates%20in%20the%20first%20range%20are%20contained%20within%20the%20second%20range.%3C%2FP%3E%3CP%3EI%20am%20using%20excel%202016%20and%20do%20not%20want%20to%20use%20macros%2C%20just%20excel%20formulas.%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDates%20Proposed%20(Range%201)%3A%26nbsp%3B%2001-Feb-21%26nbsp%3B%20to%26nbsp%3B%2007-Feb-21%3CBR%20%2F%3EDates%20Expected%20(Range%202)%3A%26nbsp%3B%2001-Jan-21%20to%26nbsp%3B%2001-May-21%3CBR%20%2F%3E%3CBR%20%2F%3EQuestion%3A%20Are%20any%20of%20the%20dates%20in%20range%201%20in%20range%202%3F%20If%20so%2C%20then%20return%20%22Y%22%3CBR%20%2F%3E%3CBR%20%2F%3EMust%20also%20work%20the%20same%20if%20there%20is%20overlap%20ie.%3CBR%20%2F%3EDates%20Proposed%20(Range%203)%3A%26nbsp%3B%2023-Dec-20%20to%26nbsp%3B%2002-Jan-21%3CBR%20%2F%3EDates%20Proposed%20(Range%204)%3A%26nbsp%3B%2001-Jan-21%20to%26nbsp%3B%2001-May-21%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2806492%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2806517%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20if%20a%20date%20contained%20in%20one%20range%20is%20present%20in%20another%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2806517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164043%22%20target%3D%22_blank%22%3E%40Felicity123%3C%2FA%3E%26nbsp%3BI%20believe%20this%20formula%20should%20work%20as%20desired%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(OR(AND(B1%3CB2%3EC2%2CC1%26gt%3BC2))%2C%22No%22%2C%22Yes%22)%3C%2FB2%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am trying to create a formula with a yes/no type outcome for the attached problem.

I have 2 date ranges and I want to know if any of the dates in the first range are contained within the second range.

I am using excel 2016 and do not want to use macros, just excel formulas.

Thank you! 

 

Dates Proposed (Range 1):  01-Feb-21  to  07-Feb-21
Dates Expected (Range 2):  01-Jan-21 to  01-May-21

Question: Are any of the dates in range 1 in range 2? If so, then return "Y"

Must also work the same if there is overlap ie.
Dates Proposed (Range 3):  23-Dec-20 to  02-Jan-21
Dates Proposed (Range 4):  01-Jan-21 to  01-May-21

2 Replies
best response confirmed by Felicity123 (Occasional Contributor)
Solution

@Felicity123 I believe this formula should work as desired:

=IF(OR(AND(B1<B2,C1<B2),AND(B1>C2,C1>C2)),"No","Yes")

 

Thank you! Works perfectly!