Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Carson, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date to date in year/month format? (Excel 2000)

    Looking for a way to take a beginning date (date of birth, work begin date, etc.), match up against the current date, and return in the format of XX years, XX months.

    For example, date of birth 4/14/1953 would come back with 50 years, 0 months

    I can use YEARFRAC but want the months in 12 month format rather than a decimal.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to date in year/month format? (Excel 2000)

    Assuming that the date of birth is in A1 and todays date is in B1 then;

    =DATEDIF(A1,B1,"y")&"YEARS "&(DATEDIF(A1,B1,"M")-(DATEDIF(A1,B1,"Y")*12)&"MONTHS")

    Good Luck
    Stats

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Location
    Carson, California, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to date in year/month format? (Excel 2000)

    Excellent! Thanks for the help. I really appreciate the condensing both formulas into one cell as well.

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

    Re: Date to date in year/month format? (Excel 2000)

    You can use the DateDif worksheet function: if the beginning date is in cell A1, use =DATEDIF(A1,TODAY(),"y") for the age in years and =DATEDIF(A1,TODAY(),"ym") for the age in months. For a formula in one cell, use =DATEDIF(A1,TODAY(),"y") & " year(s), " & DATEDIF(A1,TODAY(),"ym") & " month(s)"

    Note: I use the Dutch version of Excel. In Excel 97, DATEDIF worked, though it was not documented; in Excel 2002, it isn't recognized any more. I have never used Excel 2000.

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

    Re: Date to date in year/month format? (Excel 2000)

    There are some things to note in using DATEDIF. It is in all versions since at least Excel 5 though it has only been documented in the help file in Excel 2000 (I don't know if it is documented in Excel 2003 help file). For detailed info re this function see Chip Pearson's site

Posting Permissions

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