CODEWITHSUNDEEP
vbadateexcelexcel-formula
John W
John W

Reputation: 201

Find a previous date on a list closest to a specified date

I would like to have VBA code or formula that will take a variable date value and find it on a list range of dates. If it can't find the date I want it to find the closest previous date value. VLOOKUP works great for finding an exact match but I am struggling with the code to find the closest previous date.

My only thought is to create a loop where if it doesn't match it continues to subtract a day from the variable date until it can locate a match on the list. That does not seem like the best or most efficient method and I hope someone can recommend a simpler solution.

Example:

Variable Date = 01/26/2014

Date List
02/04/2014
01/27/2014
01/24/2014
01/13/2014
12/29/2013

Desired Result = 01/24/2014

Any help would be appreciated.

Upvotes: 4

Views: 27808

Answers (3)

Excel Ireland
Excel Ireland

Reputation: 31

I went about this a little differently, no arrays needed Find how many numbers are bigger then the one you are looking for with CountIf() Then I used =Large this will find the nth dates in a list we have the nth we are looking for in the countIF()

=LARGE(A:A,COUNTIF(A:A,">="&TODAY()))

Upvotes: 3

John Bustos
John Bustos

Reputation: 19544

Suppose your list of dates was in cells A1:A5 and your check date was in cell C1, you could enter this function ENTERED AS AN ARRAY FORMULA:

=MAX(($A$1:$A$5<=C1)*A1:A5)

Rememeber, to enter it as an array formula, hit Ctrl + Shift + Enter when entering the formula.

Hope this does the trick!!

Upvotes: 10

Anthony Bird
Anthony Bird

Reputation: 261

Vlookup can do this actually, if you set the final argument to true, which looks for an approximate match. You need your dates sorted from Oldest to newest and it will return the first date that is not after your search term.

Upvotes: 0

Related Questions