Excel Daily News

Search MrExcel

birthdays auto-changing based on date

I'm sure this is simple, but we have a youth center here and I have birthdates in one column and age in the column next to it. Is there a way the age can be automated to change as the birthday passes? Thank you!

Posted by Greg on January 08, 2001 7:54 PM

response: in the field that has the age type =(now()-A1)/365.25 That should get you pretty close to the age depending how many decimal places you have in the cell.

The second one requires that you format B1 via Format, Cells, Number tab as General.

Aladin

Posted by Celia on January 08, 2001 9:36 PM

If you just need the age in years, then :-=DATEDIF(A1,TODAY(),"y")

The formula =YEAR(TODAY())-YEAR(A1) will not work in all cicumstances. For example, if the birthdate is Dec 31 1999 and today's date is Jan 1 2001, it will produce the result 2 whereas the result required is 1.

Also, the the long formula gives the wrong number of days if the birth-day is on the last day of a short month(28, 29, or 30) and todays' day is the last day of a longer month.In such cases the days should be 0.It should, of course, be possible to take this into account in the formula but it would be a bit unwieldy. Perhaps there's a better way (without resorting to a UDF)?

Celia

Posted by Aladin Akyurek on January 08, 2001 10:09 PM

: I'm sure this is simple, but we have a youth center here and I have birthdates in one column and age in the column next to it. Is there a way the age can be automated to change as the birthday passes? Thank you!

Celia: I don't mind looking one year older, that is perhaps the reason of my sticking to the shorter formula.;-)

Cheers.

Aladin

Posted by Celia on January 08, 2001 11:53 PM

Another brainteaser

:

Ah! That may be all very well for you, but do you know any fomula that will help to make me look =>1year younger?Celia(PS. Your formula is shorter by 1 character only!)

Posted by todd on January 09, 2001 9:26 AM

this one here seems to work well. Thank you all very much for the help! =DATEDIF(A1,TODAY(),"y")

(12 responses) HI! I am using a vlookup/match to populate fields from column headers. For the most part it's doing exactly what I need except instead of pu

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View our Privacy Policy, Cookies Policy, and Terms of Use.