How to get the first 3 dates closest to the current date in Excel

Ok, this may sound kind of strange.  We are trying to find an excel formula that would compare a column of dates and identify the closest dates to the current date.

For example:

Today dates = 11/27/2017

List of dates:
09/30/2017
10/01/2017
10/10/2017
11/15/2017
11/30/2017
12/28/2017
12/31/2017


The result should be:
11/30/2017
12/28/2017
12/31/2017

We have tried it with =MAX(($B$6:$B$20<TODAY())*B6:B20), but 2 things:

1. If a "past" date is the closest, it display THAT row (we don't want that - we want "after" current date)
2. If we  get the formula, how can we calculate the "next" nearest date?
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
Hi Rayluvs,

See attached solution.

Thanks,

Alan.
EE-29070599-Version1.xlsx
0
rayluvsAuthor Commented:
Great! Thanx!

However, when applying in our excel, we get "#NUM!" (see pix below) error in the first formula (the rest is ok).  We have placed our data and the format of the cells in your excel to see what is causing the error.

excel problemEE-29070599-Version1.xlsx
0
AlanConsultantCommented:
Hi,

Enter the formula, including the equals sign, but not the braces, using shift-ctrl-enter as an array formula.

Alan.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rayluvsAuthor Commented:
It's already done.

It looks more like the actual positioning of that first cell that affects  the results.  I have included a short vid of what I am saying.  Notice that if I move those 3 formulas around, it works fine in some locations where in others don't.

What can it be?
eeExcel.Issue.zip
0
AlanConsultantCommented:
Hi Raylus,

In the formula, where it says:

ROW()-2

please replace that with ROW()-N+1

where N is the row number of the cell that you are putting the first formula in.


Thanks,

Alan.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
do you mean this?

How to find closest value greater than or less than in Excel?
https://www.extendoffice.com/documents/excel/4279-excel-find-closest-nearest-value-greater-than-less-than.html

then I come out with something attached.
EE-29070599-Version1_b.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Both options work!  
Thanx!
0
rayluvsAuthor Commented:
Gave Ryan "Best" because I didn't have to consider always adding the row to the formula.  We can move it around and same result.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
We can move it around and same result.
yes, we can implement that by freezing the cells/ range that we're referring to by placing $ before the relevant cells/ range.

more reading:

Formula Watch: Lock Excel formulas so they don’t change when you paste
https://blogs.technet.microsoft.com/hub/2011/04/26/formula-watch-lock-excel-formulas-so-they-dont-change-when-you-paste/
0
rayluvsAuthor Commented:
Thanx!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.