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

#### footitch

##### New Member
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

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### mrshl9898

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
Hi Footitch,

Does this do what you want?

Footitch.xlsx
ABC
1Anchor Start DateSprint Start DateWithin Range?
201-Jan-2003-Jan-20Yes
315-Jan-2006-Jan-20No
429-Jan-2028-Jan-20Yes
Sheet1
Cell Formulas
RangeFormula
C2:C4C2=IF(ISERROR(AGGREGATE(15,6,ROW(\$A\$2:\$A\$999)-ROW(\$A\$1)/(ABS(\$A\$2:\$A\$999-B2)<=3),1)),"No","Yes")

#### footitch

##### New Member
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?

##### Well-known Member
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.

Replies
1
Views
87
Replies
2
Views
49
Replies
1
Views
114
Replies
3
Views
86
Replies
7
Views
151