• Status: Solved
• Priority: Medium
• Security: Public
• Views: 42

# 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?
0
rayluvs
• 5
• 3
• 2
2 Solutions

ConsultantCommented:
Hi Rayluvs,

See attached solution.

Thanks,

Alan.
EE-29070599-Version1.xlsx
0

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

EE-29070599-Version1.xlsx
0

ConsultantCommented:
Hi,

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

Alan.
0

Author Commented:

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

ConsultantCommented:
Hi Raylus,

In the formula, where it says:

ROW()-2

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

Thanks,

Alan.
0

Commented:
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

Author Commented:
Both options work!
Thanx!
0

Author 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

Commented:
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.

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

Author Commented:
Thanx!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.