Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    Kentucky
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Age (Excel/2000)

    I would like to know an easy way to calculate the difference between two dates and have the answer come up as years and months. The way I currently do it is through a series of formulas, entering the month and date in seperate cells and by way of using "if/then" statements in my formula get my answer. There has got to be easier way.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating Age (Excel/2000)

    From <A target="_blank" HREF=http://www.cpearson.com/excel/datedif.htm#Age>Chip Pearson</A>, assuming there are cells containing valid dates called 'birthdate' and 'todate':

    =DATEDIF(birthdate,todate,"y") & " years, " & DATEDIF(birthdate,todate,"ym") & " months, " & DATEDIF(birthdate,todate,"md") & " days"

    Just trim off the last section if you don't want days. (I knew my own route wasn't getting there!)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Age (Excel/2000)

    What is that function, DATEDIF ?

    I don't have that function available. And, I'm assuming that you're typing this formula into the Cell in a worksheet, right?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating Age (Excel/2000)

    Chip Pearson's discussion of DATEDIF is so good I reproduce it here with acknowledgement; see the website link above.

    <font color=blue> Chip Pearson, author:
    "The DATEDIF function is a worksheet function that computes the difference between two dates. This function is available in all versions of Excel since version 5, but was never documented in the help files until Excel 2000. In Excel95 and earlier, DATEDIF resides in the Analysis ToolPak add-in module, so you must install this in order to use the function. In Excel97 and Excel2000, it is part of Excel proper, so you don't need to use the ATP.

    DATEDIF has, for whatever reason, been treated as one of the drunk cousins of the Function Family. Excel knows he lives a happy and useful existence, and will acknowledge his existence when you ask, but will never mention him in "polite" conversation.

    You can use DATEDIF as you would any normal worksheet function, because it is a normal worksheet function. Until Excel2000, it was never documented in the help files, but it has been around since at least Excel5. There has been, from time to time, an article in the Knowledge Base describing DATEDIF, but as often as not, the article is not available. Why? Who knows? One of the deep dark secrets known only the Softies."</font color=blue>

    Does this help, considering the XL version you are using?

    And, yes, entered directly in a cell, except that I specifically range-named two other cells 'birthdate' and 'todate'; you can use address references, and you can also substitute TODAY() for 'todate' if you always want to calculate age from birthdate through today.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Age (Excel/2000)

    John, can the DATEDIF Function be used to calculate age in years only with fractions rather than Years/Months/Weeksetc?

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Calculating Age (Excel/2000)

    Using John's example, you could change it to <pre> =DATEDIF(birthdate,todate,"d")/365.25</pre>

    That should work. Or if you have the Analysis Toolpak addin activated you could use <pre> =YEARFRAC(birthdate,todate,1)</pre>

    Andrew C

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating Age (Excel/2000)

    Andrew has answered, but you could do this as simply as:

    =(end_date - birthdate)/365.25

    since Excel's time calculations use days as their base.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating Age (Excel/2000)

    What if you would like to find the age on a specific date compared to there birthdate and not have it updated?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating Age (Excel/2000)

    If you enter the specific (fixed) date in a cell and use that as todate or end_date in the various formulas proposed in this thread, and the birth date in another cell used as birthdate, the formula will always return the same result.
    If you want to "freeze" the result, you can select the cell with the formula, copy it, then use Edit | Paste Special with the Values option to replace the formula with its result.

Posting Permissions

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