Thanks:  0
Likes:  0

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

1. 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. 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,

3. 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. 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

User Tag List

Posting Permissions

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