# Thread: Show last birthday date in Excel 2010

1. ## 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

2. My daughter's birthday is 29th Feb, and she tells me neither formula works for her.

zeddy

3. 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! HTH

4. On non-leap 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 non-leap 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

5. On non-leap 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

6. Hey Guys,

So many ways to skin the cat! Pity the poor cat!

7. On non-leap 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'.
These seem mutually exclusive to me. With the error, you have the idea (like in "The Pirates of Penzance") where there is NO birthday in the non-Leap year. In the second you have a longer celebration...

But you still are not a year older until AFTER Feb 28...

Steve

8. 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

9. If your birthday is 29th Feb, you have the advantage of being only 16 when everyone else is 64.
But that is not true. You have had 16 anniversaries, but you are still 64: that is the most ingenious paradox...

Steve

10. 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.."

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 date-of-birth are different).

zeddy

11. 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".
But that does not make it accurate! This reminds me of a parody I read of People magazine some decades ago. They had posed the question of who the 16th president of the United States was and the survey indicated that the majority of the people answered it was "Alan Alda" [MASH was very popular when this parody came out] indicating that it was not Abraham Lincoln at all [he was only 3rd in the survey, I don't remember who #2 was]...

My understanding is that legally most states deem it to be March 1 in non-leap 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)]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•