Need to check if date is within specific range (list of dates)

[footitch]

Background, I am checking the start dates for agile sprints against a specific range

Data is like this...

Anchor Start Date	Sprint Start Date	Within Range?
1/1/20	1/3/20
1/15/20	1/6/20
1/29/20	1/28/20

I need to run a check on all 'sprint start dates' and see if any of those dates are within 3 days (plus or minus) of any of the 'anchor start dates'. I need to have a 'yes or no' value in the 'within range' column. I've tried using DATEDIF, nested AND statements. Can't seem to get it to work.

1. So if I check 1/3/20 against the list of all anchor dates it should return 'yes'
2. If I check 1/6/20 against the list of all anchor dates it should return 'no'

Thanks all!

-joe

 

[mrshl9898]

Ugly, but functional...

=IFERROR(IF(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(MATCH(B2,$A$2:$A$4,0),MATCH(B2-1,$A$2:$A$4,0)),MATCH(B2-2,$A$2:$A$4,0)),MATCH(B2-3,$A$2:$A$4,0)),MATCH(B2+1,$A$2:$A$4,0)),MATCH(B2+2,A2:A4,0)),MATCH(B2+3,$A$2:$A$4,0))>0.1,"YES",""),"NO")

 

[rondeondo]

Hi Joe, you can try something in c2 like
if(abs(b2-a2)<=3 , "Yes","No")

when you subtract one date from another the result is the number of days.
Abs gives the absolute value, so -3 becomes 3 etc

 

[Toadstool]

Hi Footitch,

Does this do what you want?

Footitch.xlsx
	A	B	C
1	Anchor Start Date	Sprint Start Date	Within Range?
2	01-Jan-20	03-Jan-20	Yes
3	15-Jan-20	06-Jan-20	No
4	29-Jan-20	28-Jan-20	Yes
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF(ISERROR(AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/(ABS($A$2:$A$999-B2)<=3),1)),"No","Yes")

 

[footitch]

My Man, ToadStool. Many Thanks. Can you talk me through the logic? I know what the aggregate function does (kind of, not sure of all of the parameters).

• What is the 15,6 all about?
• What does the "/" do in the middle of the function?

 

[Toadstool]

Let's describe the front and back first:
=IF(ISERROR(
,"No","Yes")
Says that if the AGGREGATE returns an error (which in this case would be a #NUM error when no row number can be returned) then No, otherwise Yes

AGGREGATE has many functions but I'm using option 15 for SMALL and the 6 says don't return an error while performing the SMALL

At the end the ",1)" is the k parameter for SMALL, which will return the 1st lowest, then second, then thirds based on the value of k. In this case I am just looking for a single match so use 1.

ROW($A$2:$A$999)-ROW($A$1)
Says don't use the values of column A but rather the row numbers(I could omit the minus row one because that's usually used to get the right INDEX but we're not doing that here) so it'll check rows 2 to 999. The divide says if the divisor is TRUE then I'll get row being looked at / 1 meaning I'll get the row number. If the divisor is FALSE it divides by 0 so I get an error, which the option 6 ignores.

/(ABS($A$2:$A$999-B2)<=3)
Says divide the contents of A2 to A999 and take the ABSolute value (we don't care if it's 3 days before or after the Anchor Start) less Sprint Start for this row and if less than or equal to 3 then return TRUE.