Solved

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

Posted on 2011-02-22
8
257 Views
Last Modified: 2012-05-11
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
Comment
Question by:BBlu
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 75 total points
Comment Utility
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
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 75 total points
Comment Utility
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
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
See attached for examples of both formulas I suggested

regards, barry
26840320.xls
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 33

Expert Comment

by:jppinto
Comment Utility
Attched working example...
MinDate.xlsx
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 75 total points
Comment Utility
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

Open in new window

0
 

Author Comment

by:BBlu
Comment Utility
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 Closing Comment

by:BBlu
Comment Utility
Thank you all!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now