This years birthday date

  • Thread starter Thread starter Steffie
  • Start date Start date
S

Steffie

What function do I use to get this year's birthday date
relative to the day a person was actually born, and also
calculate his age this year.

Regards
Steffie
 
Hi Steffie
1. If the date a person was born is stored in A1 use the following
formula to get his date for this year:
=DATE(YEAR(NOW()),MONTH(A1),DAY(A1))
Note: will return the first of March in a non leap year for a person
who was born on the 29th February

2. Calculating the Age: Use (A1 stores the birth date)
=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
months, " & DATEDIF(A1,NOW(),"md") & " days"
 
Hi

Try this in B1:

=DATE(2004,MONTH(A1),DAY(A1))

To find the age use:
=B1+1-A1
and format the result as yy

Andy.
 
Hi Steffie!

With both questions answered, I'll anticipate your next question which
will be how do I calculate the next birthday?

With Birthday in A1 and the reference date in B1



=IF(DATE(YEAR(B1),MONTH(B1),DAY(B1))<DATE(YEAR(B1),MONTH(A1),DAY(A1)),
DATE(YEAR(B1),MONTH(A1),DAY(A1)),DATE(YEAR(B1)+1,MONTH(A1),DAY(A1)))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,

You could replace DATE(YEAR(B1),MONTH(B1),DAY(B1)) with B1
:-)

Also:
=MIN(MOD(DATE(YEAR(B1)+{0;1},MONTH(A1),DAY(A1))-B1,764))+B1

Regards,

Daniel M.
 
Hi Daniel!

Thanks for the modification (idiot!!) and for the alternative.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top