Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Minnesota, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Age as of this year's birthday (Excel 2000)

    I know how to use the DATEDIF function to determine age as of TODAY or NOW, but is there a way to determine the person's age as of that person's birthday in 2004? The spreadsheet that was created has a column for birthdate, but no column for 2004 birthday date, and the creator doesn't want to add that column and re-type all the birthday 2004 dates. Am I making any sense here?

    I guess what I'm asking is whether there's a way to take the birthdate (e.g., 2/10/1978 in cell B3) and, with a formula, update that to this year's birthday (2/10/2004) and use that calculated date as the comparison date in a DATEDIF function - without a column for the 2004 date. Any ideas? Impossible? Thanks for whatever help you can give.
    Karnie Moesenthin
    Technical Training and Development Professional
    Legal Industry

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

    Re: Age as of this year's birthday (Excel 2000)

    How about this:
    <pre>=DATEDIF(B3,DATE(YEAR(TODAY()),MONTH(B3),DAY( B3)),"y")</pre>


    Steve

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Age as of this year's birthday (Excel 2000)

    The simplest way does not require DATEDIF
    With the persons date of birth in A1 you could use =2004-YEAR(A1)
    Alternatively use =YEAR(TODAY())-YEAR(A1) to find what their age will be at their birthday in the current year.

  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

    Re: Age as of this year's birthday (Excel 2000)

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> This is much better than my way.
    Steve

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Age as of this year's birthday (Excel 2000)

    It's a rare day that I can find a better way than the master.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Minnesota, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age as of this year's birthday (Excel 2000)

    Thanks, Tony. I tried this with 11/14/1981 in B3, and the result appeared as 1/23/1900 0:00. The answer should be 23 years on 11/14/2004. What am I doing wrong? Is it because of the way the person entered the dates, or what? I'm afraid to use the second formula because if they open the worksheet in 2005 it would change the age, and they need it to stay as of 2004, I believe.
    Karnie Moesenthin
    Technical Training and Development Professional
    Legal Industry

  7. #7
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Age as of this year's birthday (Excel 2000)

    This is a problem with Excel. You need to format the result as a number. Excel has assumed the result will be a date.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Minnesota, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age as of this year's birthday (Excel 2000)

    You guys are wonderful. That did it. Thank you very much to both you and Steve for the quick response! That's why I love this lounge.
    Karnie Moesenthin
    Technical Training and Development Professional
    Legal Industry

  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

    Re: Age as of this year's birthday (Excel 2000)

    I am no "master".

    I am just a Chemist with some excel experience and a willingness to answer questions.

    Steve

Posting Permissions

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