SOLVED

Highlighted
Contributor

IF AND OR formula regarding Start Dates and End Dates

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

Re: IF AND OR formula regarding Start Dates and End Dates

@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

Re: IF AND OR formula regarding Start Dates and End Dates

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

Re: IF AND OR formula regarding Start Dates and End Dates

Another variant

``=IF( (MEDIAN(A2,\$F\$2:\$F\$3)=A2)+(MEDIAN(A2,\$G\$2:\$G\$3)=A2),"INCLUDE","")``
Highlighted

Re: IF AND OR formula regarding Start Dates and End Dates

This is fantastic - thank you!
Highlighted