Results 1 to 11 of 11
  1. #1
    WS Lounge VIP
    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

  2. #2
    WS Lounge VIP
    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

  3. #3
    Super Moderator RetiredGeek's Avatar
    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! HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    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. #6
    Super Moderator RetiredGeek's Avatar
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #8
    WS Lounge VIP
    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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.
    But that is not true. You have had 16 anniversaries, but you are still 64: that is the most ingenious paradox...

    Steve

  10. #10
    WS Lounge VIP
    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 date-of-birth are different).

    zeddy

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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".
    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
  •