Formula mod needed for age, year, DOB

[leopardhawk]

Okay, my brain is cramping but I'm pretty sure this problem I'm having can be solved by someone a lot brighter than I am.

The two columns in question, columns B & C, need to work together for my worksheet to be accurate. They need to be able to account for users who are younger than 70 (which they are doing now) and users who are equal to or greater than 70. The formulas in column B are working fine as long as the user is under 70 years of age. If 'personal_info'!B12 is blank, the formula returns nothing. If the user enters their DOB in 'personal_info'!B12, the formula returns the Year of Birth + 70.

What I need is, if the user enters a DOB that makes them =>70 years of age, the formula in B16 will return the current year (2019) and C16 will return the users age this year.

Currently, there are no formulas in column C but I assume I need formulas there as well no matter what the user's age is.

The end result is that if the user is younger than 70, B16 will ALWAYS show the year they turn 70 and C16 will ALWAYS show 70. If the user is over 70, B16 will show the current year and C16 will show their current age. I get a headache just thinking about it but this is very important to me so any help is greatly appreciated.

Excel 2016 (Windows) 32 bit
	B	C
16		70
17		71
18		72
19		73
rrif

Cell Formulas
Range	Formula
B16	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+70)
B17	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+71)
B18	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+72)
B19	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+73)

 

[DanteAmor]

Maybe this will help you


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:78.89px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; "> </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Result</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Age</td><td style="background-color:#ffff00; font-weight:bold; "> </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DOB</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">79</td><td > </td><td style="text-align:right; ">10/09/1939</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">74</td><td > </td><td style="text-align:right; ">10/09/1944</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">80</td><td > </td><td style="text-align:right; ">02/04/1939</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">75</td><td > </td><td style="text-align:right; ">23/05/1944</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="text-align:right; ">2020</td><td style="text-align:right; ">70</td><td > </td><td style="text-align:right; ">10/09/1950</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">70</td><td > </td><td style="text-align:right; ">10/09/1948</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">70</td><td > </td><td style="text-align:right; ">10/09/1949</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">70</td><td > </td><td style="text-align:right; ">01/01/1950</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">70</td><td > </td><td style="text-align:right; ">23/02/1949</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">71</td><td > </td><td style="text-align:right; ">25/05/1948</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(E2="","",IF(DATEDIF(E2,TODAY(),"y")<70,YEAR(TODAY())+(70-DATEDIF(E2,TODAY(),"y"))-1,YEAR(TODAY())))</td></tr><tr><td >C2</td><td >=IF(DATEDIF(E2,DATE(B2,MONTH(TODAY()),DAY(TODAY())),"y")>70,DATEDIF(E2,DATE(B2,MONTH(TODAY()),DAY(TODAY())),"y"),70)</td></tr></table></td></tr></table>

 

[leopardhawk]

DanteAmor,

This looks awesome! Thank you so much! I am still testing many scenarios. Would you mind if I run into any issues I will post them here in this thread if that is okay with you?

Thanks again!

 

[DanteAmor]

DanteAmor,

This looks awesome! Thank you so much! I am still testing many scenarios. Would you mind if I run into any issues I will post them here in this thread if that is okay with you?

Thanks again!

Of course. Let me know any questions you have and I'll review it with pleasure.

 

[leopardhawk]

DanteAmor, good morning. Okay, my testing has revealed a couple of 'hopefully' minor issues.

1. With regards to your formula for cells B2 and C2... In my original post, I said "The end result is that if the user is younger than 70, B16 will ALWAYS show the year they turn 70 and C16 will ALWAYS show 70. If the user is over 70, B16 will show the current year and C16 will show their current age." This was a mistake on my part because I should have said "The end result is that if the user is younger than 70, B16 will ALWAYS show the "END OF THE" year they turn 70 and C16 will ALWAYS show 70. If the user is over 70, B16 will show the "END OF THE" current year and C16 will show their current age "AT THE END OF THE YEAR." I have capitalized what I should have said for emphasis. Hopefully, this is a minor adjustment to the formula.

2. As you can see in my original post, there are several rows beneath the row in question. I only put four rows in my post but there are actually 70 rows below that need the formula to work in one year increments. I was hoping that once the formulas in B16 & C16 were working, that I could click-drag down to populate the rest of the rows. This does not work with the B16 formula and I am hoping that there is a solution for this as well.

I so much appreciate your help. Let me know if you need more information. Thanks!

 

[DanteAmor]

DanteAmor, good morning. Okay, my testing has revealed a couple of 'hopefully' minor issues.

1. With regards to your formula for cells B2 and C2... In my original post, I said "The end result is that if the user is younger than 70, B16 will ALWAYS show the year they turn 70 and C16 will ALWAYS show 70. If the user is over 70, B16 will show the current year and C16 will show their current age." This was a mistake on my part because I should have said "The end result is that if the user is younger than 70, B16 will ALWAYS show the "END OF THE" year they turn 70 and C16 will ALWAYS show 70. If the user is over 70, B16 will show the "END OF THE" current year and C16 will show their current age "AT THE END OF THE YEAR." I have capitalized what I should have said for emphasis. Hopefully, this is a minor adjustment to the formula.

2. As you can see in my original post, there are several rows beneath the row in question. I only put four rows in my post but there are actually 70 rows below that need the formula to work in one year increments. I was hoping that once the formulas in B16 & C16 were working, that I could click-drag down to populate the rest of the rows. This does not work with the B16 formula and I am hoping that there is a solution for this as well.

I so much appreciate your help. Let me know if you need more information. Thanks!

In my examples I tried to put all the possibilities, less, equal and greater than 70 years. Even if in this year it turns 70 years before today's date or after today's date.


You could give an example of each case and the results you expect.


The formulas can be copied down.

 

[leopardhawk]

Below is a cross section of the sheet. Your formulas are in B16 and C16 and they are returning the correct information because I entered May 6, 1933 in "personal_info'!E12. What I need is for B17 to show 2020 and C17 to show 87, B18 to show 2021 and C18 to show 88, B19 to show 2022 and C19 to show 89 and so on all the way to the bottom of the sheet (total 70 rows). When I try to copy the formula down through the rows, I get 2019 in every row of column B and nothing changes at all in column C when I copy down.

I hope this helps.

Excel 2016 (Windows) 32 bit
	B	C	D	E	F	G
13	RRIF withdrawals for Name
14	Estimated value of RRSP at end of 2019				$ 62,250.00
15	Year	Age *	Percentage **	Amount	RRIF Value ***	Monthly Withdrawals
16	2019	86	0.05	$ 3,112.50	$ 61,471.88	$ 259.38
17	2004	71	0.0528	$ 3,245.72	$ 60,531.36	$ 270.48
18	2005	72	0.054	$ 3,268.69	$ 59,532.59	$ 272.39
19	2006	73	0.0553	$ 3,292.15	$ 58,472.91	$ 274.35
20	2007	74	0.0567	$ 3,315.41	$ 57,350.23	$ 276.28
21	2008	75	0.0582	$ 3,337.78	$ 56,163.08	$ 278.15
22	2009	76	0.0598	$ 3,358.55	$ 54,910.64	$ 279.88
23	2010	77	0.0617	$ 3,387.99	$ 53,581.80	$ 282.33
24	2011	78	0.0636	$ 3,407.80	$ 52,183.32	$ 283.98
25	2012	79	0.0658	$ 3,433.66	$ 50,706.53	$ 286.14
26	2013	80	0.0682	$ 3,458.19	$ 49,149.84	$ 288.18
rrif

Cell Formulas
Range	Formula
B16	=IF(personal_info!$E$12="","",IF(DATEDIF(personal_info!$E$12,TODAY(),"y")<70,YEAR(TODAY())+(70-DATEDIF(personal_info!$E$12,TODAY(),"y"))-1,YEAR(TODAY())))
B17	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+71)
B18	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+72)
B19	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+73)
B20	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+74)
B21	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+75)
B22	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+76)
B23	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+77)
B24	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+78)
B25	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+79)
B26	=IF(personal_info!$E$12=0,"",YEAR(personal_info!$E$12)+80)
C16	=IF(DATEDIF(personal_info!$E$12,DATE(B16,MONTH(TODAY()),DAY(TODAY())),"y")>70,DATEDIF(personal_info!$E$12,DATE(B16,MONTH(TODAY()),DAY(TODAY())),"y"),70)

 

[DanteAmor]

Below is a cross section of the sheet. Your formulas are in B16 and C16 and they are returning the correct information because I entered May 6, 1933 in "personal_info'!E12. What I need is for B17 to show 2020 and C17 to show 87, B18 to show 2021 and C18 to show 88, B19 to show 2022 and C19 to show 89 and so on all the way to the bottom of the sheet (total 70 rows). When I try to copy the formula down through the rows, I get 2019 in every row of column B and nothing changes at all in column C when I copy down.

I hope this helps.

And if you add 1 in each column and copies B3 and C3 down.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:43.72px;" /><col style="width:29.47px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; "> </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Result</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Age</td><td style="background-color:#ffff00; font-weight:bold; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">2019</td><td style="text-align:right; ">79</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">2020</td><td style="text-align:right; ">80</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="text-align:right; ">2021</td><td style="text-align:right; ">81</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="text-align:right; ">2022</td><td style="text-align:right; ">82</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="text-align:right; ">2023</td><td style="text-align:right; ">83</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="text-align:right; ">2024</td><td style="text-align:right; ">84</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="text-align:right; ">2025</td><td style="text-align:right; ">85</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="text-align:right; ">2026</td><td style="text-align:right; ">86</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="text-align:right; ">2027</td><td style="text-align:right; ">87</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="text-align:right; ">2028</td><td style="text-align:right; ">88</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(personal_info!E2="","",IF(DATEDIF(personal_info!E2,TODAY(),"y")<70,YEAR(TODAY())+(70-DATEDIF(personal_info!E2,TODAY(),"y"))-1,YEAR(TODAY())))</td></tr><tr><td >C2</td><td >=IF(DATEDIF(personal_info!E2,DATE(B2,MONTH(TODAY()),DAY(TODAY())),"y")>70,DATEDIF(personal_info!E2,DATE(B2,MONTH(TODAY()),DAY(TODAY())),"y"),70)</td></tr><tr><td >B3</td><td >=B2+1</td></tr><tr><td >C3</td><td >=C2+1</td></tr></table></td></tr></table>

 

[leopardhawk]

Okay, that was fairly simple, for some reason I thought I needed a similar formula in those two columns all the way down. Thank you!

On another note, I still need the formulas in B2 and C2 to return the year and the age of the user at the END OF THAT YEAR. The way it is working now, it will return a different year and age dependent on whether or not their birthday has passed in the current year. I need the end of the year for both. Please?

Thanks!

 

[DanteAmor]

Okay, that was fairly simple, for some reason I thought I needed a similar formula in those two columns all the way down. Thank you!

On another note, I still need the formulas in B2 and C2 to return the year and the age of the user at the END OF THAT YEAR. The way it is working now, it will return a different year and age dependent on whether or not their birthday has passed in the current year. I need the end of the year for both. Please?

Thanks!

That makes these formulas, I put several examples in post #2