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))

Birthday (A)	Plan Year (B)	Age (C)	AgeUpDate (D)
2/20/1959 <tbody> </tbody>	10/1/2019 <tbody> </tbody>	59.79	10/1/2019 <tbody> </tbody>
4/17/1959	9/1/2019 <tbody> </tbody>	59.64	9/1/2019
4/19/1952	9/1/2019 <tbody> </tbody>	66.63	Not Eligible
9/17/1959	9/1/2019 <tbody> </tbody>	59.22	9/17/2019
6/20/1949	9/1/2019 <tbody> </tbody>	69.46	Not Eligible
9/27/1959	9/1/2019 <tbody> </tbody>	59.19	9/27/2019
9/25/1959	9/1/2019 <tbody> </tbody>	59.20	9/25/2019
10/27/1959	9/1/2019 <tbody> </tbody>	59.11	10/27/2019
1/17/2013	9/1/2019 <tbody> </tbody>	5.88	Not Eligible

<tbody>
</tbody>