Calculate the date someone reaches age 70.5

[LauraR]

I need to calculate the date someone reaches age 70.5. I have the birth date (of course!) but how do I add 70.5 years to a date? Since the birthdate is really a serial number, just adding 70.5 doesn't work. I need the formula to reflect that 70.5 is years - or using a month conversion would work also - the date someone is 846 months old. Thank you.

LR

 

[MrKowz]

Assuming the date is in cell A1:

=DATE(YEAR(A1)+70,MONTH(A1)+6,DAY(A1))

Pretty sure that will work.

 

[jproffer]

I don't know if MrKows formula works, but having seem some of his work before, I can assume it does.

But I do have a question.

Isn't a serial number just a number of days from the start of (whatever date system you use)?

So could you not just add ((ROUND(365 * 70.5,0)) + (ROUND(70.5/4,0))), which is 25751 to the serial number to get the same result?

I ask this before I even go and try it, so I may prove myself wrong before this even gets a reply, but it's just a question.

 

[jproffer]

I just tried it and it appears that the serial number is a number of days within the date system you use.

Adding 25751 seems to get you within a day or so of 70 years and 6 months later.

 

[LauraR]

Thanks. I was using 9/21/1938 as the test birthday. JProffer's formula returned 3/23/2009. Mr. Kowz's formula returned 3/21/2009. I mainly need to know when someone must take a required distribution from their IRA, hense 70.5 years from the birthday. Thanks for the solutions.

 

[rrdodge]

Thanks. I was using 9/21/1938 as the test birthday. JProffer's formula returned 3/23/2009. Mr. Kowz's formula returned 3/21/2009. I mainly need to know when someone must take a required distribution from their IRA, hense 70.5 years from the birthday. Thanks for the solutions.

MrKowz formula of =DATE(YEAR(A1)+70,MONTH(A1)+6,DAY(A1)) is the correct one because the IRS uses the months, not the days, to determine what is 6 months later. Therefore, July 1st is moved to January 1st of the next calendar year per the months calculation, not to December 31st of the same calendar year per the days calculation. This is based on the examples given on the web page of https://www.irs.gov/retirement-plan...nt-topics-required-minimum-distributions-rmds

Sincerely,

Ronald R. Dodge, Jr., CPA, Security+