Consulting

Results 1 to 8 of 8

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

  1. #1
    VBAX Newbie
    Joined
    Nov 2016
    Posts
    4
    Location

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

    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:

    1. Find if any of these dates exist within 30 days (or x number of days) of others in the date range
    2. 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

  2. #2
    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
    ---------------
    Hope this helps
    ---------------

    Have been away for a very long time,
    but am popping back again (now and then).

  3. #3
    VBAX Newbie
    Joined
    Nov 2016
    Posts
    4
    Location
    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?

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    1. Find if any of these dates exist within 30 days (or x number of days) of others in the date range
    2. 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"
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    VBAX Newbie
    Joined
    Nov 2016
    Posts
    4
    Location
    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"

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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."
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    VBAX Newbie
    Joined
    Nov 2016
    Posts
    4
    Location
    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?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •