SOLVED

IF AND OR formula regarding Start Dates and End Dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1433086%22%20slang%3D%22en-US%22%3EIF%20AND%20OR%20formula%20regarding%20Start%20Dates%20and%20End%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1433086%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20do%20a%20year%20on%20year%20analysis%20and%20only%20include%20accounts%20which%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Date%20Closed%20(column%20A)%20OR%20Date%20Deferred%20(column%20B)%20is%20between%20Start%20Date%20and%20End%20Date%20in%202019%20OR%202020%2C%20then%20column%20C%20must%20say%20%22INCLUDE%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20excel%20attached%2C%20hugely%20appreciate%20your%20response%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJenny%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1433086%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1433151%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20AND%20OR%20formula%20regarding%20Start%20Dates%20and%20End%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1433151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3ETry%20if%20this%20works%20for%20you%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(AND(A2%26gt%3B%3D%24F%242%2CA2%26lt%3B%3D%24F%243)%2CAND(A2%26gt%3B%3D%24G%242%2CA2%26lt%3B%3D%24G%243)%2CAND(B2%26gt%3B%3D%24F%242%2CB2%26lt%3B%3D%24F%243)%2CAND(B2%26gt%3B%3D%24G%242%2CB2%26lt%3B%3D%24G%243))%2C%22INCLUDE%22%2C%22%22)%3CBR%20%2F%3EI%20have%20included%20both%20the%20start%20and%20end%20dates%20in%20the%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1433311%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20AND%20OR%20formula%20regarding%20Start%20Dates%20and%20End%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1433311%22%20slang%3D%22en-US%22%3EAmazing!%20thank%20you%20so%20much%20and%20for%20super%20quick%20response!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1433940%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20AND%20OR%20formula%20regarding%20Start%20Dates%20and%20End%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1433940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%20(MEDIAN(A2%2C%24F%242%3A%24F%243)%3DA2)%2B(MEDIAN(A2%2C%24G%242%3A%24G%243)%3DA2)%2C%22INCLUDE%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1435920%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20AND%20OR%20formula%20regarding%20Start%20Dates%20and%20End%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1435920%22%20slang%3D%22en-US%22%3EThis%20is%20fantastic%20-%20thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1439971%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20AND%20OR%20formula%20regarding%20Start%20Dates%20and%20End%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1439971%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668941%22%20target%3D%22_blank%22%3E%40JennySommet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGlad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi everyone,

 

I want to do a year on year analysis and only include accounts which:

 

If Date Closed (column A) OR Date Deferred (column B) is between Start Date and End Date in 2019 OR 2020, then column C must say "INCLUDE"

 

Please see excel attached, hugely appreciate your response in advance!

 

Thanks

 

Jenny

5 Replies
Highlighted
Best Response confirmed by JennySommet (Contributor)
Solution

@JennySommet 
Try if this works for you 

=IF(OR(AND(A2>=$F$2,A2<=$F$3),AND(A2>=$G$2,A2<=$G$3),AND(B2>=$F$2,B2<=$F$3),AND(B2>=$G$2,B2<=$G$3)),"INCLUDE","")
I have included both the start and end dates in the formula

Highlighted
Amazing! thank you so much and for super quick response!
Highlighted

@JennySommet 

Another variant

=IF( (MEDIAN(A2,$F$2:$F$3)=A2)+(MEDIAN(A2,$G$2:$G$3)=A2),"INCLUDE","")
Highlighted
This is fantastic - thank you!
Highlighted