Rolling Window Based on birth month

[danwhiteinak]

Ok, Im new so please don't eat me alive but after searching the forum and old Mr google, I can't figure out the answer. I am using Excel 2007 with SP3 installed. I have a requirement to track data in two windows of time based on birth month. Essentially window one of time is the first day of the month after the birth month for the last day of the 6th month. For example if my birth month was Jun, period 1 would be from 1 JUL14 until 31 DEC 14. Period 2 is then the first day of month 7 until the last day of month 12. However, both of these windows need to be relevant to todays date. So in the example above period 2 would actually be from 1 Jan 14 to 30 Jun14, and then after 30 JUN 14 period 2 needs to roll to 1 JAN 15 to 30JUN 15. Does this make sense? Essentially it boils down to based off of cell A1 (birthdate) I need cell A2 to be beginning of period 1, A3 to be end of period 1, B2 to be beginning of period 2, B3 to be end of period 2. I have tried using EOMONTH and DATE function but with rolling dates essentially all based off of todays date I can't figure it out. Any help would be appreciated.

 

[danwhiteinak]

[TABLE="class: grid, width: 408"]
<tbody>[TR]
[TD]Today
[/TD]
[TD]31-Jan-14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birthdate
[/TD]
[TD]1-Jan-84
[/TD]
[TD]2-Aug-87
[/TD]
[TD]11-Nov-83
[/TD]
[TD]10-Jun-84
[/TD]
[TD]15-Dec-87
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Feb-14
[/TD]
[TD]1-Sep-13
[/TD]
[TD]1-Dec-13
[/TD]
[TD]1-Jul-14
[/TD]
[TD]1-Jan-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Jul-14
[/TD]
[TD]28-Feb-14
[/TD]
[TD]31-May-14
[/TD]
[TD]31-Dec-14
[/TD]
[TD]30-Jun-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Aug-13
[/TD]
[TD]1-Mar-14
[/TD]
[TD]1-Jun-14
[/TD]
[TD]1-Jan-14
[/TD]
[TD]1-Jul-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Jan-14
[/TD]
[TD]30-Aug-14
[/TD]
[TD]30-Nov-14
[/TD]
[TD]30-Jun-14
[/TD]
[TD]31-Dec-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Today
[/TD]
[TD]10-Jun-14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birthdate
[/TD]
[TD]1-Jan-84
[/TD]
[TD]2-Aug-87
[/TD]
[TD]11-Nov-83
[/TD]
[TD]10-Jun-84
[/TD]
[TD]15-Dec-87
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Feb-14
[/TD]
[TD]1-Sep-14
[/TD]
[TD]1-Dec-14
[/TD]
[TD]1-Jul-14
[/TD]
[TD]1-Jan-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Jul-14
[/TD]
[TD]28-Feb-15
[/TD]
[TD]31-May-15
[/TD]
[TD]31-Dec-14
[/TD]
[TD]30-Jun-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Aug-14
[/TD]
[TD]1-Mar-14
[/TD]
[TD]1-Jun-14
[/TD]
[TD]1-Jan-14
[/TD]
[TD]1-Jul-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Jan-15
[/TD]
[TD]30-Aug-14
[/TD]
[TD]30-Nov-14
[/TD]
[TD]30-Jun-14
[/TD]
[TD]31-Dec-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Today
[/TD]
[TD]2-Jul-14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birthdate
[/TD]
[TD]1-Jan-84
[/TD]
[TD]2-Aug-87
[/TD]
[TD]11-Nov-83
[/TD]
[TD]10-Jun-84
[/TD]
[TD]15-Dec-87
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Feb-14
[/TD]
[TD]1-Sep-14
[/TD]
[TD]1-Dec-14
[/TD]
[TD]1-Jul-14
[/TD]
[TD]1-Jan-15
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Jul-14
[/TD]
[TD]28-Feb-15
[/TD]
[TD]31-May-15
[/TD]
[TD]31-Dec-14
[/TD]
[TD]30-Jun-15
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Aug-14
[/TD]
[TD]1-Mar-14
[/TD]
[TD]1-Jun-14
[/TD]
[TD]1-Jan-15
[/TD]
[TD]1-Jul-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Jan-15
[/TD]
[TD]30-Aug-14
[/TD]
[TD]30-Nov-14
[/TD]
[TD]30-Jun-15
[/TD]
[TD]31-Dec-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Today
[/TD]
[TD]20-Dec-14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birthdate
[/TD]
[TD]1-Jan-84
[/TD]
[TD]2-Aug-87
[/TD]
[TD]11-Nov-83
[/TD]
[TD]10-Jun-84
[/TD]
[TD]15-Dec-87
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Feb-15
[/TD]
[TD]1-Sep-14
[/TD]
[TD]1-Dec-14
[/TD]
[TD]1-Jul-14
[/TD]
[TD]1-Jan-15
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Jul-15
[/TD]
[TD]28-Feb-15
[/TD]
[TD]31-May-15
[/TD]
[TD]31-Dec-14
[/TD]
[TD]30-Jun-15
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Aug-14
[/TD]
[TD]1-Mar-15
[/TD]
[TD]1-Jun-15
[/TD]
[TD]1-Jan-15
[/TD]
[TD]1-Jul-14
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Jan-15
[/TD]
[TD]30-Aug-15
[/TD]
[TD]30-Nov-15
[/TD]
[TD]30-Jun-15
[/TD]
[TD]31-Dec-
14
[/TD]
[/TR]
</tbody>[/TABLE]

So here is that table filled out. In these examples, the red text indicates which period the person is currently in. To asnwer another question, yes period 2 could be behind period one, depending on the time of year. Basically, the period the person is not in should always be in the future. In addition, period 1 should always start the first day of the month after the birthmonth, the problem is whether it is this year or the future year.

 

[markmzz]

Maybe this:

Layout

[TABLE="width: 264"]
<tbody>[TR]
[TD="width: 45, bgcolor: transparent"]Today[/TD]
[TD="width: 61, bgcolor: transparent, align: right"]30/04/14[/TD]
[TD="width: 61, bgcolor: transparent"][/TD]
[TD="width: 61, bgcolor: transparent"][/TD]
[TD="width: 61, bgcolor: transparent"][/TD]
[TD="width: 61, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Birth[/TD]
[TD="bgcolor: transparent, align: right"]01/01/84[/TD]
[TD="bgcolor: transparent, align: right"]02/08/87[/TD]
[TD="bgcolor: transparent, align: right"]11/11/83[/TD]
[TD="bgcolor: transparent, align: right"]10/06/84[/TD]
[TD="bgcolor: transparent, align: right"]15/12/87[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P1-Beg[/TD]
[TD="bgcolor: yellow, align: right"]01/02/14[/TD]
[TD="bgcolor: yellow, align: right"]01/09/14[/TD]
[TD="bgcolor: yellow, align: right"]01/12/13[/TD]
[TD="bgcolor: yellow, align: right"]01/07/14[/TD]
[TD="bgcolor: yellow, align: right"]01/01/14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P1-End[/TD]
[TD="bgcolor: yellow, align: right"]31/07/14[/TD]
[TD="bgcolor: yellow, align: right"]28/02/15[/TD]
[TD="bgcolor: yellow, align: right"]31/05/14[/TD]
[TD="bgcolor: yellow, align: right"]31/12/14[/TD]
[TD="bgcolor: yellow, align: right"]30/06/14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-Beg[/TD]
[TD="bgcolor: yellow, align: right"]01/08/14[/TD]
[TD="bgcolor: yellow, align: right"]01/03/14[/TD]
[TD="bgcolor: yellow, align: right"]01/06/14[/TD]
[TD="bgcolor: yellow, align: right"]01/01/14[/TD]
[TD="bgcolor: yellow, align: right"]01/07/14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-End[/TD]
[TD="bgcolor: yellow, align: right"]31/01/15[/TD]
[TD="bgcolor: yellow, align: right"]31/08/14[/TD]
[TD="bgcolor: yellow, align: right"]30/11/14[/TD]
[TD="bgcolor: yellow, align: right"]30/06/14[/TD]
[TD="bgcolor: yellow, align: right"]31/12/14[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*******[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[/TR]
</tbody>[/TABLE]

Formulas

In B3

=EOMONTH(MONTH(B2)&"/"&YEAR($B$1)-LOOKUP($B$1,EOMONTH(MONTH(B2)&"/"&YEAR($B$1),{-1000;-6;12;12})+{0;1;0;1},{1;0;0;1})+(MONTH($B$1)=12)*(MONTH(B2)=1),0)+1

Or

=EOMONTH(MONTH(B2)&"/"&YEAR($B$1)-ISERROR(MATCH($B$1,
INDEX(ROW(INDIRECT(EOMONTH(MONTH(B2)&"/"&YEAR($B$1),-6)+1&":"&EOMONTH(MONTH(B2)&"/"&YEAR($B$1),12))),),0))+(MONTH($B$1)=12)*(MONTH(B2)=1),0)+1

In B4

=EOMONTH(B3,5)

In B5

=EOMONTH(B3,IF(OR($B$1 < B3,$B$1 > EOMONTH(B3,12)),-7,5))+1

In B6

=EOMONTH(B5,5)

Markmzz</b3,$b$1>

 

[danwhiteinak]

WOW, you are awesome. It appears to work very well. I am going to change the date on my computer and verify that through-out the year it works, but everything appears to be correct. Thank you so much.

 

[markmzz]

WOW, you are awesome. It appears to work very well. I am going to change the date on my computer and verify that through-out the year it works, but everything appears to be correct. Thank you so much.

You are welcome and thanks for the feedback.

Markmzz