Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Date Question: Convert 7.45 years to "Hrs" / "Months" forma

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello again,

    I have a quick question. I am trying to determine the "difference" in Years and Months between two end dates (ie, subtract 1/2/1994 from 4/2/2002). When I subtract I get a decimal approximation (ie, 8.25.. where that would be roughly 8 years 3 months). HOw would I get Excel to convert this decimal value to a "YY years , MM months" format?

    Any help would be greatly appreciated.

    Thanks,

    Rob

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-02 14:02, Mopacs wrote:
    Hello again,

    I have a quick question. I am trying to determine the "difference" in Years and Months between two end dates (ie, subtract 1/2/1994 from 4/2/2002). When I subtract I get a decimal approximation (ie, 8.25.. where that would be roughly 8 years 3 months). HOw would I get Excel to convert this decimal value to a "YY years , MM months" format?

    Any help would be greatly appreciated.

    Thanks,

    Rob
    Will this formula work for you (assumes the later date is in cell B1):

    =YEAR(B1)-YEAR(A1)+MIN(0,SIGN(MONTH(B1)-MONTH(A1)))&" years, "&MONTH(B1)-MONTH(A1)+MIN(0,SIGN(DAY(B1)-DAY(A1)))+MIN(0,SIGN(MONTH(B1)-MONTH(A1)))*-12&" months, "&IF(DAY(B1)-DAY(A1)<0,EOMONTH(A1,0)-A1+DAY(B1),DAY(B1)-DAY(A1))&" days"

    Regards,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-02 14:02, Mopacs wrote:
    Hello again,

    I have a quick question. I am trying to determine the "difference" in Years and Months between two end dates (ie, subtract 1/2/1994 from 4/2/2002). When I subtract I get a decimal approximation (ie, 8.25.. where that would be roughly 8 years 3 months). HOw would I get Excel to convert this decimal value to a "YY years , MM months" format?

    Any help would be greatly appreciated.

    Thanks,

    Rob

    Rob,


    here are 2 ways to get years and months 1 text 1 formatting

    In both cases D8 contains the difference in cell dates.

    =INT(D8/365)&" Years, "&INT((D8-(365*INT(D8/365)))/30.25)&" Months"

    If you want exactly years and months put this formula in

    =TEXT(D8,"yy:mm")

    Hope This Helps



  4. #4
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A simpler solution would be to custom format the cell.

    yy" years",m" months" as a custom format will yield 08 years,3 months.

    yy:m will yield 8:3

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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