Date of birth, end of year issues (2)

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
847
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Thank you so much! Both seem to be working fine after having passed some fairly rigorous testing. I really appreciate it.

Cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,372
Messages
5,641,761
Members
417,234
Latest member
aaryan bl

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?

Disable AdBlock

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
Top