• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Grouping Dates

C

chloec

Member
Hello - I would appreciate help with the following problem.


1. I have a rolling 12 months: (Year-Month) My months are listed at the start of the month (1 June 2011) but item 3 needs to account for the WHOLE month (June 1, 2011 - June 30, 2011)

6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/1/2011 11/1/2011 12/1/2011 1/1/2012 2/1/2012 3/1/2012 4/1/2012 5/1/2012

2. I have a series of Dates:

8/25/11

3/20/12

8/25/11

5/18/12


3. I would like to write a formula where each date (item #2) is evaluated to see if it falls in a series of ranges. If the date falls in the last 3 months (march 2012 thru May 2012) - write 3 months. If it falls withn the last 6 months (Dec 2011 thru May 2012) - write 6 months. If it falls within the rolling 12 months (then write 12 months). For example - if a date is May 31, 2012 - it needs to display 12,6, and 3 months. If it is Dec 10, 2011 - it needs to display in the 12, 6 month category.

NOTE - THE DATE NEEDS TO BE EVALUATED TO THE END OF THE CURRENT MONTH. I can't change the dates in section #1.


I have been struggling with this a lot so I appreciate any assistance!
 
Hi, chloec!


Let's suppose you have the data like this:


-----

[pre]
Code: 
Roll 12 mth	EOMonth	        Series of dates	Match	Falling range
01/06/2011	30/06/2011	25/08/2011	2	12
01/07/2011	31/07/2011	20/03/2012	9	3
01/08/2011	31/08/2011	25/08/2011	2	12
01/09/2011	30/09/2011	18/05/2012	11	3
01/10/2011	31/10/2011
01/11/2011	30/11/2011
01/12/2011	31/12/2011
01/01/2012	31/01/2012
01/02/2012	29/02/2012
01/03/2012	31/03/2012
01/04/2012	30/04/2012
01/05/2012	31/05/2012
[/pre]
-----


Column A : your rolling 12 months

Column B : =FIN.MES(A2;0) -----> in english: =EOMONTH(A2,0)

Column C : your series of dates

Column D : =COINCIDIR(C2;B$2:B$14;1) -----> in english: =MATCH(C2,B$2:B$14,1)

Column E : =SI(D2<=3;12;SI(D2<=6;6;SI(D2<=12;3;0))) -----> in english: =IF(D2<=3,12,IF(D2<=6,6,IF(D2<=12,3,0)))


Regards!
 
I'm not going to have EO Month column (B) can we account for this?

THank you so much for the assist, SirJB7!
 
I believe SirJB7 was implying that you will need to create one. However, I don't think it's needed. Using this layout:

[pre]
Code: 
Month List	Chosen Dates	Falling Range
1-Jun-11	25-Aug-11	12
1-Jul-11	20-Mar-12	3
1-Aug-11	25-Aug-11	12
1-Sep-11	18-May-12	3
1-Oct-11
1-Nov-11
1-Dec-11
1-Jan-12
1-Feb-12
1-Mar-12
1-Apr-12
1-May-12
[/pre]
Formula in C2 is:

=IF(COUNTIF($A$2:$A$13,">="&EOMONTH(B2,-1)+1)>6,12,IF(COUNTIF($A$2:$A$13,">="&EOMONTH(B2,-1)+1)>3,6,3))
 
Hi, chloec!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top