How to find if a date exists within 30 days of other dates in a date range in Excel

[NathanA]

I have a series of dates (kept as date, month, and year) located in columns K to Z in Microsoft Excel. There are a different amount of dates in each row. I need a formula/code (not PivotTable) to:


Find if any of these dates exist within 30 days (or x number of days) of others in the date range
Find the difference in days between the two closest dates

There have been similar examples, but they haven't used the data range as the condition, so I've been getting error messages.
The following is an excerpt of data from columns K3:U8.
30/09/2016 24/08/2016 10/08/2016 20/07/2016 22/06/2016 8/06/2016 25/05/2016 27/04/2016 13/04/2016 30/03/2016
27/06/2016 13/06/2016 30/05/2016 16/05/2016 2/05/2016 18/04/2016 4/04/2016 14/03/2016 29/02/2016 16/02/2016
21/06/2016 16/05/2016 19/04/2016 15/03/2016
5/12/2016 14/11/2016 2/11/2016 17/10/2016 3/10/2016 19/09/2016 5/09/2016 15/08/2016 8/08/2016 25/07/2016 11/07/2016
14/12/2016 16/11/2016 2/11/2016 23/08/2016 26/07/2016
13/12/2016 29/11/2016 18/10/2016 23/08/2016 19/07/2016

[onlyadrafter]

Hello,

Does this work as required. I assume columns I and J are available to enter results, Column I for you point 2 and Column J for your point 1.


Sub DATES() For MY_ROWS = 3 To Range("K" & Rows.Count).End(xlUp).Row
MY_LATEST_DIFF = 1E+255
For MY_COLS = Cells(MY_ROWS, Columns.Count).End(xlToLeft).Column To 12 Step -1
MY_DIFF = Cells(MY_ROWS, MY_COLS).Value - Cells(MY_ROWS, MY_COLS - 1).Value
If MY_DIFF < 0 Then MY_DIFF = MY_DIFF * -1
If MY_DIFF < 30 Then LESS30 = "YES"
If MY_DIFF < MY_LATEST_DIFF Then MY_LATEST_DIFF = MY_DIFF
Next MY_COLS
Cells(MY_ROWS, 10).Value = LESS30
Cells(MY_ROWS, 9).Value = MY_LATEST_DIFF
Next MY_ROWS
End Sub

[NathanA]

Thanks for replying. I ran the code and for the example I provided column I was populated with 1E+255 for the last 4 rows. Rather than the code returning an absolute number, can it find if any of the dates within each row exist within x number of days of each other, and the number of days between the two closest dates in that row?

[SamT]

One problem is that many of those values may not be Date Type Values, they might beString Types that look (to Humans) like dates.

You can test this by Selecting all of them and then "Format Cells + Number" If they all turn to 5 digit numbers, they are all dates. You can revert them all to Dates by pressing Ctrl+Z.

Please let us know if they are not all Dates. We will have to write code to convert them to Dates before we can continue.

[SamT]

Find if any of these dates exist within 30 days (or x number of days) of others in the date range
Find the difference in days between the two closest dates



1) when you say "DateRange" do you mean?
a) all rows and columns
b) one Row
c) One Column

2) "between the two closest dates" Same as my question 1


3) What information do you want in the report?
The simplest is "Within x Days:=Yes/No" and "Minimum Difference:=n Days"

[NathanA]

1. In one row. i.e. in row 1, how many of the dates are within 30 days of each other? in row 2, how many of the dates are within 30 days of each other?

Row 1: 30/09/2016 24/08/2016 10/08/2016 20/07/2016 22/06/2016 8/06/2016 25/05/2016 27/04/2016 13/04/2016 30/03/2016
Row 2: 27/06/2016 13/06/2016 30/05/2016 16/05/2016 2/05/2016 18/04/2016 4/04/2016 14/03/2016 29/02/2016 16/02/2016

2. In one row. i.e. in row 1, what is the minimum number of days between 2 dates? in row 2, what is the minimum number of days between 2 dates?

3. Yes to the second, but regarding the first, "How many within x Days: y"

[SamT]

Did you test to see if they are real Excel Dates? Excel Dates display as a number when formatted as "Number." Excel Dates only display as a date, when formatted as "Date."

[NathanA]

Yes. The minimum number of days part of the code is working (thank you!), but is it possible to change the "YES" answer to a number?