Check and Capture first date not in order

vpranitha

New Member
Joined
Jun 26, 2014
Messages
25
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.


Month Security
Jan-17YYYYY
Apr-17YYYYY
May-17YYYYY
Jun-17YYYYY
Jul-17YYYYY
Aug-17YYYYY
Sep-17YYYYY
Oct-17YYYYY
Nov-17YYYYY
Dec-17YYYYY
Jan-18YYYYY
Feb-18YYYYY
Mar-18YYYYY
Apr-18YYYYY
May-18YYYYY
Jun-18YYYYY

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>


Appreciate any assistance.

Thank you
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Not the prettiest of formulas...My assumption is that those dates are all on the first (1st) of the month


Book1
BCDE
1MonthSecurity
21-Jan-17YYYYY1-Apr-17
31-Apr-17YYYYY
41-May-17YYYYY
51-Jun-17YYYYY
61-Jul-17YYYYY
71-Aug-17YYYYY
81-Sep-17YYYYY
91-Oct-17YYYYY
101-Nov-17YYYYY
111-Dec-17YYYYY
121-Jan-18YYYYY
131-Feb-18YYYYY
141-Mar-18YYYYY
151-Apr-18YYYYY
161-May-18YYYYY
171-Jun-18YYYYY
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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,),))
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,215,873
Messages
6,127,464
Members
449,384
Latest member
purevega

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top