Checking a date against a set of dates for those <=X months

[MrMiller]

Windows 7
Excel 2010

I would like to compare a list of dates (attributed to a single item, in "sheet1") against a list of dates (attributed to the same item, in "sheet2") and return which of the dates in "sheet1" are within X months (will be using 18 and 60 months) of any of the dates in "sheet2". The total number of dates in each data-set is different.


For context: I'm comparing lists of inspection dates (two different types of inspections) to see what does/doesn't qualify for a safety credit which requires completing the two events within X months of each other.



I successfully made a set of 'IF' statements to compare a list (on "sheet1") against a single date (on "sheet2") but am unsure how to incorporate the second variable.

 

[MrMiller]

For anyone who's curious, I found a solution in the form of a matrix.

Two header rows and two header columns, as well as a "Final Verdict" column. (So data starts in cell D4)

The cells in the matrix compared their respective column headers to their respective row headers:

• First - it verifies the "item" is the same, if this test fails, the whole line returns a blank and will be ignored in later commands
• Second - it uses a set of redundant IFs (1 for 18+, 1 for 18- ) to find dates outside the +/- 18 month range

=IF($A4=D$1,IF($B4>=D$2,IF((ABS($B4-D$2)/30.5)>18,"Yes","No"),IF((ABS(D$2-$B4)/30.5)>18,"Yes","No")),"")


This returns a "Yes", "No", or (Blank).

Then the "Final Verdict" column looks at its respective row. (I found this command on the internet) It searches for "No"s (an important trick!) because if a row contains even 1 "No" then it is disqualified from the distance rule. It can have as many "Yes"s as it wants, more "Yes"s simply means its far away from more data. This search also indirectly deals with the (Blanks). It returns a statement which is easy to read, and I added a highlight using Conditional Formatting just for fun (not shown below).

=IF(SUMPRODUCT(ISNUMBER(SEARCH("No~",D4:AW4))+0)>0,"No On-Stream Required","On-Stream Required")

The "~" is a wild card! If you want to grab just part of the text in a cell (perhaps you want more descriptive results in your matrix) use: "~No", "No~" "~No~"
​

The "Final Verdict" column is the feature which makes the data easy to read, other than that it doesn't serve much functionality.


Feel free to offer suggestions if you see something I missed or got wrong!