Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2000
    Location
    Wisbech, Cambridgeshire, United Kingdom
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculating age in Excel 2000 (2000 SR1A)

    I have a problem which must have a simple solution, which I cannot see. I want to calculate ages in complete years to determine whether an individual qualifies for a particular award. I have the date of birth in a cell, and the qualifying date in another. If I deduct the qualifying date from the birth date, I get the age in days. If I divide this by 365 and round down the result, I ought to get the age in years on the qualifying date. However, if the birth date is just after the quakifying date, the incidence of leap years will affect the result. For example, if the birth date is 12 April 1936, and the qualifying date is 5 April 2001, Excel gives me an age of 65, when the answer should be 64.

    If I were to divide by 365.25, I should probably get the right answer, but this still seems to be imprecise. Is there a better way of doing this?

    Regards, Howard Merkin

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

    Re: calculating age in Excel 2000 (2000 SR1A)

    You can use the DATEDIF function for this.

    If the date of birth is in cell A1, and the qualifying date in cell A2, use the formula

    =DATEDIF(A1,A2,"y")

    to get the age in years.

  3. #3
    Lounger
    Join Date
    Dec 2000
    Location
    Wisbech, Cambridgeshire, United Kingdom
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating age in Excel 2000 (2000 SR1A)

    Thank you Hans for your very prompt reply.

    DATEDIF works perfectly, and does exactly what I need. It is extraordinary that there is nothing about this function in Excel's list of Date and Time Functions, or in the All Functions list. If you go to the Help file Index, DATEDIF appears, and is adequately described. I see that Microsoft say that it is provided for compatibility with Lotus 1-2-3 - a comment which makes me a little sad that I gave up Lotus for Microsoft about ten years ago!

    Regards, Howard Merkin

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

    Re: calculating age in Excel 2000 (2000 SR1A)

    Howard,

    Until Excel 2000, DATEDIF wasn't documented at all! See http://www.cpearson.com/excel/datedif.htm for som more info about the function.

    Regards,
    Hans

Posting Permissions

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