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

1. 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!

2. 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?

3. 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.

5. 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. 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. 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. 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.

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

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