+ Reply to Thread
Results 1 to 8 of 8

Search date range +/- 7 days and return date that falls within range

  1. #1
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Search date range +/- 7 days and return date that falls within range

    I have a table of dates arranged in a vertical column. I would to enter a date and find the first date that is within 15 days of that date (-7 date +7). If no date in the table is within 15 days of the search date, I would like to return #N/A. Note that most of the dates in the look up column are separated by more than 7 days, but not all. I would prefer to use a native Excel function rather than a UDF, if possible.

    See attached workbook for desired results and data.

    Thanks!
    Attached Files Attached Files
    If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
    Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...

    Thomas Lafferty
    Analyst/Programmer

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,744

    Re: Search date range +/- 7 days and return date that falls within range

    which date are you searching on, and which column are you searching IN?

    Maybe if you gave a sample, it will be easier to see what you want?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Search date range +/- 7 days and return date that falls within range

    Thanks for the reply, and sorry about not being clearer. Here's what I'm after. In the A column of the book I attached, I have a search date. In the B column, I show what it should return when examining the dates in the G column. The dates in the D column are also being checked against the G column dates.

    Looking at cell A2, I can't find any date in the G column that is within 15 days, so I put #N/A in B2. The first date in the A colunmn that is within 15 days of any date in the G column is in cell A6, and the corresponding date is 10/4/2014. No other date is within 15 days of that date in the G column.

    Let me know if you need additional clarification, and thanks again for the reply.

  4. #4
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Search date range +/- 7 days and return date that falls within range

    Bump... any takers? Need more information?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,744

    Re: Search date range +/- 7 days and return date that falls within range

    forum rules require that you wait at least a day before bumping

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,744

    Re: Search date range +/- 7 days and return date that falls within range

    Im still working onthis, but take a look at following, copied down...
    =IF(COUNTIFS(G:G,">="&A2-7,G:G,"<="&A2+7)>0,INDEX(G:G,IFERROR(MATCH(A2,G:G,-1),MATCH(A2,G:G,1)),1),"")

  7. #7
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Search date range +/- 7 days and return date that falls within range

    Thank you for your work on this! I'm away from my workstation till sometime tomorrow, But I will check it out first chance I get and will let you know.

  8. #8
    Forum Contributor
    Join Date
    07-24-2009
    Location
    Valrico, FL USA
    MS-Off Ver
    Excel 2016
    Posts
    358

    Re: Search date range +/- 7 days and return date that falls within range

    Try the formula below as an array formula (Control+Shift+Enter) in cell B2 and copy down.

    =INDEX($G$2:$G$46,MATCH(1,($G$2:$G$46>=A2-7)*($G$2:$G$46<=A2+7),0))

    It returns the results indicated in the column B group but differs slightly in the column E group.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1