Formula mod needed for age, year, DOB

leopardhawk

Well-known Member
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 Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

DanteAmor

Well-known Member

<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

Well-known Member
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

Well-known Member
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

Well-known Member

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.

DanteAmor

Well-known Member
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.

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

Well-known Member

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.

DanteAmor

Well-known Member
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

Well-known Member
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

Well-known Member
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

Last edited:

Replies
0
Views
127
Replies
0
Views
450
Replies
16
Views
1K
Replies
3
Views
410
Replies
3
Views
433

1,129,916
Messages
5,638,972
Members
417,063
Latest member
thematulaak

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.

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

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