Check and Capture first date not in order

[vpranitha]

Hello,

Have a column representing dates in order of months a security is in a portfolio. Some months the security is not in the portfolio. Would like a formula to capture the first month the security comes back in the portfolio after a break. Eg listed below the security is absent between Jan 2017- April 2017. Would like to capture in a cell the April 2017 formulaically.


[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Month [/TD]
[TD="class: xl64, width: 64"]Security [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jan-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Apr-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]May-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jun-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jul-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Aug-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Sep-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Oct-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Nov-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Dec-17[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jan-18[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Feb-18[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Mar-18[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Apr-18[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]May-18[/TD]
[TD="class: xl66"]YYYYY[/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]Jun-18[/TD]
[TD="class: xl68"]YYYYY[/TD]
[/TR]
</tbody>[/TABLE]


Appreciate any assistance.

Thank you

 

[Momentman]

Not the prettiest of formulas...My assumption is that those dates are all on the first (1st) of the month

Book1
	B	C	D	E
1	Month	Security
2	1-Jan-17	YYYYY		1-Apr-17
3	1-Apr-17	YYYYY
4	1-May-17	YYYYY
5	1-Jun-17	YYYYY
6	1-Jul-17	YYYYY
7	1-Aug-17	YYYYY
8	1-Sep-17	YYYYY
9	1-Oct-17	YYYYY
10	1-Nov-17	YYYYY
11	1-Dec-17	YYYYY
12	1-Jan-18	YYYYY
13	1-Feb-18	YYYYY
14	1-Mar-18	YYYYY
15	1-Apr-18	YYYYY
16	1-May-18	YYYYY
17	1-Jun-18	YYYYY
Sheet2

Cell Formulas
Range	Formula
E2	{=INDEX($B$2:$B$17,MATCH(FALSE,EOMONTH($B$2,ROW(INDIRECT("1:" & COUNTA($B$1:$B$17)))-1)+1=B3:B17,0)+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tetra201]

A shorter formula for the same assumption and layout as in Post # 2:

=INDEX(B3:B17,MATCH(TRUE,INDEX(DATEDIF(B2:B16,B3:B17,"m")>1,),))

 

[Rick Rothstein]

I believe this array-entered** formula will also work...

=MIN(IF(MONTH(A3:A17)-MONTH(A2:A16)>1,A3:A17))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Note: When adjusting the range for larger data sets, the two 17's should be replaced by the last row with data, the 16 should be replaced with one less than that row number.