CODEWITHSUNDEEP
arraysexcelexcel-formula
CATSandCATSandCATS
CATSandCATSandCATS

Reputation: 312

Comparing two dates in array based on criteria in Excel

I am trying to find a formula which will compare two dates (Plan 1 minus Plan 2) and determine if they are within ninety days of each other.

The formula I have so far:

{=IF(E1<>"", IF(AND(MIN(IF(C:C=C1, IFERROR(E1-G1, -2), 0))<91, MIN(IF(C:C=C1, IFERROR(E1-G1, -2), 0))>-1), 1, 0), 0)}

The idea is that the array will loop though the Subjects in Column C and for every subject with a non-blank Plan 1 field (Column E), it will loop through the Plan 2 dates in Column G to find the closest one within ninety days. If one exists, the equation will give the Plan 1 a 1.

However, the formula above is not able to handle the dates seen below where there is no exact matching date in Column G.

Example data

Thank you for your help.

Scott Craner found the delightful equation below to answer this problem, except for the issue in the image below. It appears to be including a date greater than 90 days (orange highlight).

enter image description here

Upvotes: 0

Views: 341

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use AGGREGATE:

=IF(E2<>"",IFERROR(--(AGGREGATE(15,7,(E2-$G$2:$G$21)/(($C$2:$C$21=C2)*(E2-$G$2:$G$21>=0)),1)<91),0),0)

![enter image description here

Upvotes: 1

Related Questions