Date of birth, end of year issues (2)

[leopardhawk]

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.

=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.

=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.

 

[6StringJazzer]

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:

=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:

=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]

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

Cheers!