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

[Mopacs]

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

[Barrie Davidson]

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,

[s-o-s]

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

[Steve Hartman]

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