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

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
Asked:
rayluvs
  • 5
  • 3
  • 2
2 Solutions
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now