MrExcel Publishing
Your One Stop for Excel Tips & Solutions

birthdays auto-changing based on date


Posted by todd on January 08, 2001 6:01 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!


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.

Posted by Aladin Akyurek on January 08, 2001 8:03 PM

If the first birthday is in A1, then enter

B1 =DATEDIF(A16,TODAY(),"y")&" years, "&DATEDIF(A16,TODAY(),"ym")&" months, "&DATEDIF(A16,TODAY(),"md")&" days" (See 7583.html)

or enter the simpler formula

B1 =YEAR(TODAY())-YEAR(A16)

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")