Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel date formula (Excel)

    You guys have done an outstanding job in finding me two formulas already, this is great. I have one more for you, my apologies for the seemingly simplicity of this question:

    1. Based on the attached sample worksheet, I am trying to figure a formula to calcuate the age of the annuitant at the date the application was signed.

    Thanks again!
    L

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

    Re: Excel date formula (Excel)

    The formula is rather long because of error checking (you have missing values, invalid dates etc.). In C2, enter the formula

    =IF(OR(ISBLANK(A2),ISBLANK(B2),ISERROR(YEAR(B2)-YEAR(A2))),"",YEAR(B2)-YEAR(A2)-(MONTH(B2)<=MONTH(A2))+AND(MONTH(B2)=MONTH(A2),DAY (B2)>=DAY(A2)))

    and fill down as far as needed.

  3. #3
    New Lounger
    Join Date
    Aug 2005
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel date formula (Excel)

    Hans,

    Does the formula change if I edit the information to make a 'clean' spreadsheet?? See attached.

    Thanks!
    Lori

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

    Re: Excel date formula (Excel)

    Sorry, I don't understand the question, and the workbook looks the same as before.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel date formula (Excel)

    The same formula should still work fine.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel date formula (Excel)

    For fun I thought I may give you an alternative solution <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. This solution will give you give you their age in years, months and days:

    =IF(B2="","",DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, " & DATEDIF(A2,B2,"md") & " days")

    Enjoy.
    Jerry

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

    Re: Excel date formula (Excel)

    Ah yes, DATEDIF is much easier.

    For others reading this: DATEDIF is not documented in most Excel versions. A description can be found on Chip Pearson's site: DATEDIF Function.

Posting Permissions

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