Results 1 to 11 of 11

20130912, 11:15 #1
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,822
 Thanks
 134
 Thanked 481 Times in 458 Posts
Show last birthday date in Excel 2010
So now we have CLOSED threads before I stick my version in???
If the person's birthdate is in Cell A1, the birthday for the current year is:
=DATE(YEAR(TODAY()), MONTH(A1), DAY(A1))
Steve
=TEXT(A1,"dd mmm")+0
zeddy

20130912, 11:30 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,822
 Thanks
 134
 Thanked 481 Times in 458 Posts
My daughter's birthday is 29th Feb, and she tells me neither formula works for her.
zeddy

20130912, 12:07 #3
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,434
 Thanks
 372
 Thanked 1,457 Times in 1,326 Posts
Ok so you want to go there do you?
First you have to calculate if the current year is a leap year or not. The proper definition of a leap year is any year that is evenly dividable by 4 that is NOT an century (e.g. 2100, 2300, etc.) unless it is evenly dividable by 400 (thus 2000 was a leap year even though it was a century).
Formula to calculate Leap Year:
=OR(AND(MOD(YEAR(NOW()),4)=0,MOD(YEAR(NOW()),100)> 0),MOD(YEAR(NOW()),400)=0)
Ok now that we know if it is a leap year we can calculate the Anniversary day as:
=DATE(YEAR(TODAY()), MONTH(A2),IF( AND(NOT($D$2),MONTH(A2)=2,DAY(A2)=29),28,DAY(A2)))
LeapYear.JPG
You guessed it I didn't retire till AFTER Y2K...Big Mistake! HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20130912, 12:26 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
On nonleap years, Zeddy gives an error, mine gives March 1 which I think is correct. 2/28 is the day before the anniversary date. The anniversary of the birth is the day after Feb 28 which is Mar 1 in nonleap years and Feb 29 in leap years.
FYI I use a shorter formula to calculate if a year is a leap year:
=MONTH(DATE(YEAR(TODAY()),2,29))=2
Steve

20130912, 13:12 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,822
 Thanks
 134
 Thanked 481 Times in 458 Posts
On nonleap years, by making the error stand out, I believed my solution was better than disguising the birthday as 1st Mar.
And my daughter celebrates by having the whole week as a 'birthday week', including 'birthday eve' and 'birthday boxing day'.
zeddy

20130912, 13:54 #6
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,434
 Thanks
 372
 Thanked 1,457 Times in 1,326 Posts
Hey Guys,
So many ways to skin the cat! Pity the poor cat!May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20130912, 16:05 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
On nonleap years, by making the error stand out, I believed my solution was better than disguising the birthday as 1st Mar.
And my daughter celebrates by having the whole week as a 'birthday week', including 'birthday eve' and 'birthday boxing day'.
But you still are not a year older until AFTER Feb 28...
Steve

20130912, 18:06 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,822
 Thanks
 134
 Thanked 481 Times in 458 Posts
Steve
If your birthday is 29th Feb, you have the advantage of being only 16 when everyone else is 64.
So there.
And anyway, I just wanted to keep this thread going for longer than that one that was CLOSED.
zeddy

20130912, 19:15 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
If your birthday is 29th Feb, you have the advantage of being only 16 when everyone else is 64.
Steve

20130913, 08:51 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,822
 Thanks
 134
 Thanked 481 Times in 458 Posts
Hi Steve
The most ingenious paradox is time dilation.
Astronauts that return from missions on the International Space Station have aged less than the mission control crew that remained on Earth.
If you could get yourself up to about 97% of the speed of light, you too could age just one year while everyone at home aged four years.
As my applied math teacher once said: "Consider a spherical cow.."
..But enough of that already.
I have conducted a comprehensive survey, of one person, born on 29th Feb.
I asked "When is you birthday?" and 100% replied
"On the last day of February".
As a result of this witness statement, I vote for RG's solution as the best (so far) for the original question posed.
Unless the Queen is involved (her official birthday and dateofbirth are different).
zeddy

20130913, 11:20 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I have conducted a comprehensive survey, of one person, born on 29th Feb.
I asked "When is you birthday?" and 100% replied
"On the last day of February".
My understanding is that legally most states deem it to be March 1 in nonleap years (so you may need a different formula depending on which state the person resides in) since from Feb 29, 2000 to Feb 28, 2001 an entire year has not occurred so the age does not go up to the next year. Look at the number of days that have passed...
Steve
["most ingenious paradox" is a quote from the Pirates of Penzance song (http://www.stlyrics.com/lyrics/pirat...ratefold.htm)]