Compare Dates --From List of Dates Sorted Descending, Determine "Last" Date Greater Than Comparison Date -- Excel 2010

[supertemp123]

Hi,

In Excel 2010, I am trying to create a calculator to do the following:

Compare one date ("control date") to a list of dates ("list dates") and determine which date from that list is the first one to occur after the date in question (i.e. the last "greater than" date when looking at a descending list of dates).

The "list dates" are generally one date a year, but not always.

I have tried:

1. Subtracting the "list dates" from the "control dates" and copying that formula down until I hit a negative number.
   • This brings up the negative dates problem, however, and I can't change my workbook to the 1904 dates, since the actual dates will be recorded on a worksheet that will later be printed (a "written record" of the calculation). For these last cell formulas, can it just look for the last "valid" cell (i.e. not #NUM!)?
2. Using an IF statement to designate those dates that are GREATER THAN the "control date" (and setting TRUE to "1" and FALSE to "0") and noting where these become 0/False.
   • I see the last cell before it becomes "0", but how do I record it in another cell?

I am not having luck with ".End(xlup)" -- is this how I should approach this -- I don't want last cell with any value, but last cell with a particular value (i.e. greater than 0/Positive/True)? I am not a VB expert, but could, in a pinch, perhaps get it to work.

 

[Jonmo1]

Welcome to the board.

Here's how you can do it with a Formula

=INDEX(F1:F10,MATCH(A1,F1:F10,-1))

A1 = Control Date
F1:F10 = List Dates (sorted DEscending)

Hope that helps.

 

[JoeMo]

If your dates are sorted in descending order:

Excel Workbook
	A	B	C
1	Dates	Ctrl Date	FirstAfter
2	6/25/2014	2/7/2013	3/2/2013
3	5/26/2014
4	4/26/2014
5	3/27/2014
6	2/25/2014
7	1/26/2014
8	12/27/2013
9	11/27/2013
10	10/28/2013
11	9/28/2013
12	8/29/2013
13	7/30/2013
14	6/30/2013
15	5/31/2013
16	5/1/2013
17	4/1/2013
18	3/2/2013
19	1/31/2013
20	1/1/2013
Sheet5

 

[supertemp123]

Thank you very much. This worked great -- much simpler than the convoluted logic I used (described later).

Do you know how I would do the opposite? Meaning take another control date (this one later than the first one), matching it against the same list (sorted descending), and finding the first date BEFORE the date in question? In other words, or this second number, the period does NOT count the index date that is outside the date range (the range between control date #1 and control date #2) -- the last "list date" counted in the period is the last date BEFORE control date # 2.

The way I attacked the problem was to run the same formula against the list for both dates and find the "union" where date #1 is greater and date #2 is not greater.

=ADDRESS(MAX(IF(NumRange=MAX(NumRange),ROW(NumRange))),COLUMN(NumRange),4)

I did an "IF TRUE" test for this date greater than the other for control #1 and control #2, indicated where the LIST DATE was greater than the control date for both control numbers. From that, I did a concatenate and figured out the UNION of dates where the control date #1 was greater than the list date AND the dates where the control date #2 was less than the list date. The "TRUEFALSE" subset of dates.

LIST DATES: CONTROL DATE #1 -- 10/15/94 (ED -- Early Date) -- List Date is Greater? CONTROL DATE #2 -- 5/17/06 (LD -- Later Date) -- List Date is Greater?
12/1/2012 TRUE TRUE
12/1/2011 TRUE TRUE
12/1/2010 TRUE TRUE
12/1/2009 TRUE TRUE
12/1/2008 TRUE TRUE
12/1/2007 TRUE TRUE
12/1/2006 TRUE TRUE
12/1/2005 TRUE FALSE
12/1/2004 TRUE FALSE
12/1/2003 TRUE FALSE
12/1/2002 TRUE FALSE
12/1/2001 TRUE FALSE
12/1/2000 TRUE FALSE
12/1/1999 TRUE FALSE
12/1/1998 TRUE FALSE
12/1/1997 TRUE FALSE
12/1/1996 TRUE FALSE
12/1/1995 TRUE FALSE
12/1/1994 TRUE FALSE
12/1/1993 FALSE FALSE
12/1/1992 FALSE FALSE

From this "subset" of the greater list, however, I need to somehow indicate on a separate spreadsheet whichever dates are in this subset .. e.g. A1 is date #1 (here 12/1/95), A2 is date #2 (here 12/1/96) -- ascending or descending doesn't matter -- just need to generate this "report". How do I tell the spreadsheet to "copy" this result to another sheet? Is this a Macro? VBA? I need the report to obviously allow enough space for however many dates there are in the period being examined.

The other column of the report would be the column with the rates -- would this just be a VLOOKUP against the table with dates and rates?

e.g.

12/1/95 8%
12/1/96 9%
12/1/97 4%

I apologize for asking ... I am not the normal person at work who has to do these things. I hope to contribute myself once I have more to offer the community.

Thanks again for all of your help (and JonMo's help).