Flag Earliest Date in Array

[ExcelRose]

Hey All,

Looking for a way to aggregate all as of dates into one descriptor based on its open date. For example, if I have 2 accounts with as of dates of 3/1/18 and 7/1/18 and account open dates of 3/28/18 and 6/29/18, I want those as of dates under "M1" or month1. For these same accounts, I'd like the following month as of dates (4/1/18 and 8/1/18) to be "M2". See image below.

Dummy.png


Basically the as of dates are the end of month balance for that month and I want to know how many were funded in M1 and M2. This may be convuluted so I attached dummy data.

I basically tried setting the account open date to the first day of that month and used this as an account open date2. I then matched account open date2 with the as of date array.

The problem I'm running into under the "My Solution" part is that there shoud be an as of date for the month the account opened (accounts opened in June should have an as of date of June). However, due to some factors, the earliest as of date might be 1 or more months after the account opened.

I highlighted in yellow this issue. If my approach was way off I'm open to new ones.

This was more difficult to explain than I had thought. My apologies!

[protonLeah]

1. Column D custom formatted with "M"##
2. D3:

=IFERROR(DATEDIF(MIN($B3,EOMONTH($B3,-1)+1),$C3,"m"),0)+1

[ExcelRose]

It looks like you're running into the same issue as I am where cell D8 in your spreadsheet should be "M1" and not "M2"

[FlameRetired]

Since the opening condition is IF(I8=H8,"M1", and column I values never equal H values (except row 14) so can never return "M1".

What am I still missing?

[FlameRetired]

This returns the desired output. I am not certain it follows the intended logic.

It "fixes" the last "as of date" per account number as the start date in DATEDIF. In column L.

="M"&DATEDIF(INDEX($C$3:$C$14,MATCH(2,INDEX(1/(A3=$A$3:$A$14),0),1)),C3,"M")+1