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.

 

[Trouttrap2]

Welcome to the Boards
I don't understand your second period. but this should do your first period. you might be able to figure the second period out yourself by how the first period was constructed.

begin period 1: =EOMONTH(A1,0)+1
End period 1: =EOMONTH(A1,6)

 

[danwhiteinak]

No, that didnt work because the year is still messed up. This is what I have for period one, which appears to work.

Birthdate is in A1.
A2 (begin period 1) =DATE(YEAR(NOW()),MONTH(A1)+1,DAY(1))
A3 (end period 1) =IF(MOMTH(A1)<=6,(DATE(YEAR(NOW()),MONTH(A1)+7,0)),(DATE(YEAR(NOW()+1),MONTH(A1)+7,0)))

The problem is with B2 (Begin Period 2), and B3 (End Period 2).

Essentially, this is the other 6 month not in period 1, but depending on todays date we are either in period 2, or period 2 is the next 6 months out.

For Example:

Birthdate: 1 Jan 84
Period 1: 1 FEB 14 - 31 JUL 14
Period 2: 1 AUG 14 - 31 JAN 15

But example #2

Birthdate: 2 AUG 87
Period1: 1 SEP 14 - 28 FEB 15
Period2: 1 MAR 14 - 30 AUG 14

Does that make a little more sense?

 

[danwhiteinak]

So Cell A1 Has the Birthday.

A2 (beginning of period 1)is: =DATE(YEAR(TODAY()),MONTH(A1)+1,DAY(1))
A3 (end of Period 1)is:= IF(MONTH(A1)<=6,(DATE(YEAR(TODAY()),MONTH(A1)+7,0)),(DATE(YEAR(TODAY()+1),MONTH(A1)+7,0)))
B2 (beginning of period 2)is: =IF(AND(TODAY()>=B1,TODAY()<=C1),(IF(MONTH(A1)<=5,(DATE(YEAR(TODAY()),MONTH(A1)+7,DAY(1))),DATE(YEAR(TODAY()),MONTH(A1)-5,DAY(1)))),DATE(YEAR(B1),MONTH(B1)-6,1))
B3 (end of Period 2)is:= =DATE(YEAR(B2),MONTH(B2)+6,DAY(0))

The problem is that if the birthday is greater than 6 months from today the indivudal isnt in either window.

In continuing with the above examples

Example 3

Birthdate: 11 NOV 83
Period1: 1 DEC 13 - 31 MAY 14
Period2: 1 JUN 14 - 31 NOV 14

 

[shg]

Is the last column correct? If not, what should it be?

	A​	B​	C​	D​	E​
1​	DOB​	01 Jan 1984​	02 Aug 1987​	15 Dec 1987​	D1: Input
2​	Pd1 Beg​	01 Feb 2014​	01 Sep 2014​	01 Jan 2015​	D2: =DATE(YEAR(TODAY()), MONTH(D1)+1, 1)
3​	Pd1 End​	31 Jul 2014​	28 Feb 2015​	30 Jun 2015​	D3: =EOMONTH(D2, 5)
4​	Pd2 Beg​	01 Aug 2014​	01 Mar 2015​	01 Jul 2015​	D4: =D3 + 1
5​	Pd2 End​	31 Jan 2015​	31 Aug 2015​	31 Dec 2015​	D5: =EOMONTH(D4, 5)

 

[danwhiteinak]

A2 (beginning of period 1)is: =DATE(YEAR(TODAY()),MONTH(A1)+1,DAY(1))
A3 (end of Period 1)is:= IF(MONTH(A1)<=6,(DATE(YEAR(TODAY()),MONTH(A1)+7,0)),(DATE(YEAR(TODAY()+1),MONTH(A1)+7,0)))
B2 (beginning of period 2)is: =IF(AND(TODAY()>=A2,TODAY()<=A3),(IF(MONTH(A1)<=5,(DATE(YEAR(TODAY()),MONTH(A1)+7,DAY(1))),DATE(YEAR(TODAY()),MONTH(A1)-5,DAY(1)))),DATE(YEAR(B1),MONTH(B1)-6,1))
B3 (end of Period 2)is:= =DATE(YEAR(B2),MONTH(B2)+6,DAY(0))

Sorry screwed up the formula when I copied it.

 

[danwhiteinak]

	A​	B​	C​	D​	E​
1​	DOB​	01 Jan 1984​	02 Aug 1987​	15 Dec 1987​	D1: Input
2​	Pd1 Beg​	01 Feb 2014​	01 Sep 2014​	01 Jan 2015​	(This is the one thats messed up I believe) See my Example 3
3​	Pd1 End​	31 Jul 2014​	28 Feb 2015​	30 Jun 2015​	D3: =EOMONTH(D2, 5)
4​	Pd2 Beg​	01 Aug 2014​	01 Mar 2015​	01 Jul 2015​	D4:=IF(AND(TODAY()>=B2,TODAY()<=B3),(IF(MONTH(B1)<=5,(DATE(YEAR(TODAY()),MONTH(B1)+7,DAY(1))),DATE(YEAR(TODAY()),MONTH(B1)-5,DAY(1)))),DATE(YEAR(B1),MONTH(B1)-6,1))
5​	Pd2 End​	31 Jan 2015​	31 Aug 2015​	31 Dec 2015​	D5: =EOMONTH(D4, 5)

<tbody>
</tbody>

The calculations for reaching the end of the periods seem correct (D3 and D5). Its determining which year D2 should be in, this year, last year, or next year, based on the relative reference of Todays date vs the birthdate. The easiest way to sum it up is that no matter the time of year (date) the person should always be in a window.

 

[shg]

So what's the correct answer?

 

[markmzz]

Hi Danwhiteinak,

Could you post the desired results in the cases below?

Today	31/01/14
Birth	01/01/84	02/08/87	11/11/83	10/06/84	15/12/87
P1-Beg
P1-End
P2-Beg
P2-End
*
Today	10/06/14
Birth	01/01/84	02/08/87	11/11/83	10/06/84	15/12/87
P1-Beg
P1-End
P2-Beg
P2-End
*
Today	02/07/14
Birth	01/01/84	02/08/87	11/11/83	10/06/84	15/12/87
P1-Beg
P1-End
P2-Beg
P2-End
*
Today	20/12/14
Birth	01/01/84	02/08/87	11/11/83	10/06/84	15/12/87
P1-Beg
P1-End
P2-Beg
P2-End
*******	**********	**********	**********	**********	**********

<tbody>
</tbody>

Markmzz

 

[shg]

Birthdate: 2 AUG 87
Period1: 1 SEP 14 - 28 FEB 15
Period2: 1 MAR 14 - 30 AUG 14

Does that make a little more sense?

So period 1 can be after period 2??