# Date of birth, end of year issues (2)

#### leopardhawk

##### Well-known Member
Both of these formulas are working (no errors) but not quite up to the standards that I want for the workbook.

The first formula looks at a DOB on worksheet ‘personal_info’!E12 and if it’s blank, returns a blank. Otherwise, it will return a ‘year’ that is based on the DOB of the user. If they are less than 70 years of age, it will always return the year that they turn 70. If they are 70 years of age or older, it returns the current year. The problem with this formula for me is that I want the formula to always return the year they turn 70 but based on the END of the year they turn 70.

For example, I entered a DOB of May 11, 1970. Because May 11, 2019 has already passed, the formula returns 2039. If I change the date to September 11, 1970, the formula returns 2040 as the year. I want it to return 2040 for the entire calendar year regardless of the month/day of the birth date.

Code:
``=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())))``

The second formula is related in that it shows the age of the user. The formula looks at a DOB on worksheet ‘personal_info’!E12 and if it’s blank OR if the user is less than 70 years of age, returns 70 (this is what I want). If the user is less than 70, it will always return 70. If they are 70 years of age or older, it will return their current age but just like the first formula, I want it to be at the end of the year that they turn whatever age they are.

For example, I entered a DOB of May 11, 1938. Because May 11, 2019 has already passed, the formula returns 81. If I change the date to September 11, 1938, the formula returns 80 as the age. I want it to return 81 for the entire calendar year regardless of the month/day of the birth date.

Code:
``=IF(personal_info!\$E\$12="",70,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))``

I hope this makes sense and is any easy fix.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### 6StringJazzer

##### Well-known Member
In reading your description I can see that you are getting hung up on the DOB, whether the date has passed, and "end of the year." Forget about the dates and just deal with the year. Note my revised wording, which then makes it easy to design the formula:

For the first formula you want the greater of the current year, or the year 70 years after the birth year:

Code:
``=IF([COLOR=#333333]personal_info!\$E\$12[/COLOR]="","",MAX(YEAR(TODAY()),YEAR([COLOR=#333333]personal_info!\$E\$12[/COLOR])+70))``

For the second formula, you want the greater of 70, or the age at the end of the current year:

Code:
``=IF([COLOR=#333333]personal_info!\$E\$12[/COLOR]="","",MAX(70,YEAR(TODAY())-YEAR([COLOR=#333333]personal_info!\$E\$12[/COLOR])))``

I tested this on several dates including your examples.

#### leopardhawk

##### Well-known Member
Thank you so much! Both seem to be working fine after having passed some fairly rigorous testing. I really appreciate it.

Cheers!

Replies
3
Views
88
Replies
25
Views
985
Replies
1
Views
110
Replies
3
Views
121
Replies
9
Views
90

1,130,163
Messages
5,640,492
Members
417,148
Latest member
pe3087te

### 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?

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