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

footitch

New Member
Joined
Sep 3, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. MacOS
Background, I am checking the start dates for agile sprints against a specific range

Data is like this...


Anchor Start DateSprint Start DateWithin Range?
1/1/201/3/20
1/15/201/6/20
1/29/201/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
 

Some videos you may like

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
Joined
Feb 6, 2012
Messages
1,550
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
Joined
Aug 15, 2012
Messages
156
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 3, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. MacOS
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,020
Messages
5,575,610
Members
412,679
Latest member
TSpan
Top