Age as of this year’s birthday (Excel 2000)

Topic

New Reply

I know how to use the DATEDIF function to determine age as of TODAY or NOW, but is there a way to determine the person’s age as of that person’s birthday in 2004? The spreadsheet that was created has a column for birthdate, but no column for 2004 birthday date, and the creator doesn’t want to add that column and re-type all the birthday 2004 dates. Am I making any sense here?

I guess what I’m asking is whether there’s a way to take the birthdate (e.g., 2/10/1978 in cell B3) and, with a formula, update that to this year’s birthday (2/10/2004) and use that calculated date as the comparison date in a DATEDIF function – without a column for the 2004 date. Any ideas? Impossible? Thanks for whatever help you can give.

Reply | Quote

Replies

How about this:

=DATEDIF(B3,DATE(YEAR(TODAY()),MONTH(B3),DAY(B3)),"y")

Steve

Reply | Quote

How about this:

=DATEDIF(B3,DATE(YEAR(TODAY()),MONTH(B3),DAY(B3)),"y")

Steve

Reply | Quote

The simplest way does not require DATEDIF
With the persons date of birth in A1 you could use =2004-YEAR(A1)
Alternatively use =YEAR(TODAY())-YEAR(A1) to find what their age will be at their birthday in the current year.

Reply | Quote

This is much better than my way.
Steve

Reply | Quote

It’s a rare day that I can find a better way than the master.

Reply | Quote

I am no “master”.

I am just a Chemist with some excel experience and a willingness to answer questions.

Steve

Reply | Quote

I am no “master”.

I am just a Chemist with some excel experience and a willingness to answer questions.

Steve

Reply | Quote

It’s a rare day that I can find a better way than the master.

Reply | Quote

This is much better than my way.
Steve

Reply | Quote

Thanks, Tony. I tried this with 11/14/1981 in B3, and the result appeared as 1/23/1900 0:00. The answer should be 23 years on 11/14/2004. What am I doing wrong? Is it because of the way the person entered the dates, or what? I’m afraid to use the second formula because if they open the worksheet in 2005 it would change the age, and they need it to stay as of 2004, I believe.

Reply | Quote

This is a problem with Excel. You need to format the result as a number. Excel has assumed the result will be a date.

Reply | Quote

You guys are wonderful. That did it. Thank you very much to both you and Steve for the quick response! That’s why I love this lounge.

Reply | Quote

You guys are wonderful. That did it. Thank you very much to both you and Steve for the quick response! That’s why I love this lounge.

Reply | Quote

This is a problem with Excel. You need to format the result as a number. Excel has assumed the result will be a date.

Reply | Quote

Thanks, Tony. I tried this with 11/14/1981 in B3, and the result appeared as 1/23/1900 0:00. The answer should be 23 years on 11/14/2004. What am I doing wrong? Is it because of the way the person entered the dates, or what? I’m afraid to use the second formula because if they open the worksheet in 2005 it would change the age, and they need it to stay as of 2004, I believe.

Reply | Quote

The simplest way does not require DATEDIF
With the persons date of birth in A1 you could use =2004-YEAR(A1)
Alternatively use =YEAR(TODAY())-YEAR(A1) to find what their age will be at their birthday in the current year.

Reply | Quote