Rolling Window Based on birth month

D

danwhiteinak

New Member
Joined
Apr 9, 2014
Messages
7
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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)
 
Upvote 0
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
Is the last column correct? If not, what should it be?

[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]
DOB​
[/td][td]
01 Jan 1984​
[/td][td]
02 Aug 1987​
[/td][td]
15 Dec 1987​
[/td][td]D1: Input[/td][/tr]

[tr][td]
2​
[/td][td]
Pd1 Beg​
[/td][td]
01 Feb 2014​
[/td][td]
01 Sep 2014​
[/td][td]
01 Jan 2015​
[/td][td]D2: =DATE(YEAR(TODAY()), MONTH(D1)+1, 1)[/td][/tr]

[tr][td]
3​
[/td][td]
Pd1 End​
[/td][td]
31 Jul 2014​
[/td][td]
28 Feb 2015​
[/td][td]
30 Jun 2015​
[/td][td]D3: =EOMONTH(D2, 5)[/td][/tr]

[tr][td]
4​
[/td][td]
Pd2 Beg​
[/td][td]
01 Aug 2014​
[/td][td]
01 Mar 2015​
[/td][td]
01 Jul 2015​
[/td][td]D4: =D3 + 1[/td][/tr]

[tr][td]
5​
[/td][td]
Pd2 End​
[/td][td]
31 Jan 2015​
[/td][td]
31 Aug 2015​
[/td][td]
31 Dec 2015​
[/td][td]D5: =EOMONTH(D4, 5)[/td][/tr]
[/table]
 
Upvote 0
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.
 
Upvote 0
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
DOB​
[/TD]
[TD]
01 Jan 1984​
[/TD]
[TD]
02 Aug 1987​
[/TD]
[TD]
15 Dec 1987​
[/TD]
[TD]D1: Input
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
Pd1 Beg​
[/TD]
[TD]
01 Feb 2014​
[/TD]
[TD]
01 Sep 2014​
[/TD]
[TD]
01 Jan 2015​
[/TD]
[TD](This is the one thats messed up I believe) See my Example 3
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
Pd1 End​
[/TD]
[TD]
31 Jul 2014​
[/TD]
[TD]
28 Feb 2015​
[/TD]
[TD]
30 Jun 2015​
[/TD]
[TD]D3: =EOMONTH(D2, 5)
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
Pd2 Beg​
[/TD]
[TD]
01 Aug 2014​
[/TD]
[TD]
01 Mar 2015​
[/TD]
[TD]
01 Jul 2015​
[/TD]
[TD]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))
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
Pd2 End​
[/TD]
[TD]
31 Jan 2015​
[/TD]
[TD]
31 Aug 2015​
[/TD]
[TD]
31 Dec 2015​
[/TD]
[TD]D5: =EOMONTH(D4, 5)
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Last edited:
Upvote 0
Hi Danwhiteinak,

Could you post the desired results in the cases below?

[TABLE="width: 339"]
<tbody>[TR]
[TD="width: 75, bgcolor: transparent"]Today[/TD]
[TD="width: 75, bgcolor: transparent, align: right"]31/01/14[/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 75, 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: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P1-End[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-Beg[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-End[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/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]
[TR]
[TD="bgcolor: transparent"]Today[/TD]
[TD="bgcolor: transparent, align: right"]10/06/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="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: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P1-End[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-Beg[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-End[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/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]
[TR]
[TD="bgcolor: transparent"]Today[/TD]
[TD="bgcolor: transparent, align: right"]02/07/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="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: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P1-End[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-Beg[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-End[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/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]
[TR]
[TD="bgcolor: transparent"]Today[/TD]
[TD="bgcolor: transparent, align: right"]20/12/14[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="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: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P1-End[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-Beg[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P2-End[/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/TD]
[TD="bgcolor: #00B0F0"][/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]


Markmzz
 
Upvote 0
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?
Click to expand...
So period 1 can be after period 2??
 
Upvote 0
You must log in or register to reply here.

Similar threads

SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
139
Alex Blakenburg
A
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
322
Trixterz
T
O
  • Solved
Dynamic (row) selection in index formula based on date selection
2
Replies
10
Views
456
DanteAmor
DanteAmor
K
  • Solved
Prorating Salary based on hire date and Term date - by month
Replies
4
Views
877
KH Consulting
K
J
  • Question Question
Calculation based on value in table based on date range
Replies
0
Views
227
jsand
J

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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