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

# Way to find first date that occurs after another date in a column

I have a column with dates in it.  I'd like to find the earliest date that occurs after 12/31/08.  In other words, if I have:
1/4/08
2/23/08
4/3/08
12/30/08
4/1/09
5/8/09
11/25/09
1/1/10
5/6/10
12/4/10
1/23/11

I'd like it to return: 4/1/09
0
BBlu
• 2
• 2
• 2
• +1
3 Solutions

Commented:
If you can sort the dates, the the VLOOKUP is perfect for this:

if B1 =12/31/08
B2 =VLOOKUP(B1, \$A\$1:\$A\$100, 1)
0

Commented:
Hello rspahitz,

That will return the largest date on or before 12/31/08, in this case that's 12/30/08

BBlu

Assuming the dates are in ascending order like your example then you can use this formula

=INDEX(A2:A12,MATCH(C2+1,A2:A12)+(LOOKUP(C2+1,A2:A12)<>C2+1))

or an "array formula" to do the same which works with the dates in any order

=MIN(IF(A2:A12>C2,A2:A12))

format result cell as date

where A2:A12 contains your dates and C2 the date to match (12/31/08 in your example)

regards, barry
0

Commented:
If your values are on column A, put the 12/31/08 value on cell C2 and on cell C1 put this formula:

=MIN(IF(A1:A10>C2;A1:A10))

After you entered the formula in the formula bar you have to make it an array formlua by pressing CTRL+SHIFT+ENTER. Your formula should look like this:

{=MIN(IF(A1:A10>C2;A1:A10))}

jppinto
0

Commented:
See attached for examples of both formulas I suggested

regards, barry
26840320.xls
0

Commented:
Attched working example...
MinDate.xlsx
0

Commented:
Barry, you're right (you knew that!) that the date returned with mine will be the highest date that does not EXCEED the specified date.

This will work, if you insert a new column B with a simple sequence of 1 for the first date, 2 for the next date, etc (again, assuming the dates are sorted):

=INDIRECT("A"&VLOOKUP(C1,\$A\$1:\$B\$100,2)+1)

``````1/4/2008	1	12/31/2008
2/23/2008	2	4/1/2009 (formula in message)
4/3/2008	3
12/30/2008	4
4/1/2009	5
5/8/2009	6
11/25/2009	7
1/1/2010	8
5/6/2010	9
12/4/2010	10
1/23/2011	11
``````
0

Author Commented:
Thank you all.  I was working along the lines of the min() function with an array formula, but failed to think of the if statement part.  I also like the vlookup suggestion because it allows for finding the nth date.  Thank you all.  As usual, you guys are amazing.
0

Author Commented:
Thank you all!
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.