Rounding the Year for Vol Life

[MarlaBridges80]

I am working on a spreadsheet and I want to round the year for Voluntary Life Product. Basically, I want to take the birthdate and round it to a specific date so that the person doesn't age up until that specific date. For example. If the person is 59 a will turn 60 on their DOB of 5.9.19, but the policy says person doesn't age up until the new plan year on 10.1.19. At 60 this person will be in a new age band so how do I make a formula for the year to round? Any formula ideas for this? I also would like the formula be perpetual so I won't have to change it every year. Ideas please.

 

[Legacy 436357]

Do you still need help?

 

[Legacy 436357]

Maybe like this:

Excel Workbook
	A	B	C	D	E
1	Name	Birthday	Intervals	Added Interval	VLPD
2	Bob Johnson	05/09/1959	60	05/09/2019	10/01/2019
3			61	05/09/2020	10/01/2020
4			62	05/09/2021	10/01/2021
Sheet1

 

[GR00007]

Would calculating the age-up date help?
Column C (Age): =(TODAY() - A2) / 365.25
Column D (Age-up): =IF(OR(C2<59,C2 > 60),"Not Elegible",IF(A2 + (60 * 365.25) > B2,A2 + (60 * 365.25),B2))
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Birthday (A)[/TD]
[TD]Plan Year (B)[/TD]
[TD]Age (C)[/TD]
[TD]AgeUpDate (D)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"]2/20/1959[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]10/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]59.79[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"]10/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4/17/1959[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]9/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]59.64[/TD]
[TD]9/1/2019[/TD]
[/TR]
[TR]
[TD]4/19/1952[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]9/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]66.63[/TD]
[TD]Not Eligible[/TD]
[/TR]
[TR]
[TD]9/17/1959[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]9/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]59.22[/TD]
[TD]9/17/2019[/TD]
[/TR]
[TR]
[TD]6/20/1949[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]9/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]69.46[/TD]
[TD]Not Eligible[/TD]
[/TR]
[TR]
[TD]9/27/1959[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]9/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]59.19[/TD]
[TD]9/27/2019[/TD]
[/TR]
[TR]
[TD]9/25/1959[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]9/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]59.20[/TD]
[TD]9/25/2019[/TD]
[/TR]
[TR]
[TD]10/27/1959[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]9/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]59.11[/TD]
[TD]10/27/2019[/TD]
[/TR]
[TR]
[TD]1/17/2013[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="width: 68"]9/1/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5.88[/TD]
[TD]Not Eligible[/TD]
[/TR]
</tbody>[/TABLE]