formula help

Hello.

If I have a figure such as 4.6666667, that I would like expressed in
Years and Months, to the nearest month, what formula would I use to do
this? Is it possible in Excel?

Cheers

John

Originally Posted by John

Hello.

If I have a figure such as 4.6666667, that I would like expressed in
Years and Months, to the nearest month, what formula would I use to do
this? Is it possible in Excel?

Cheers

John

John:

In your worksheet, exactly what month does 4.6666667 represent? And what numbers before and after represent the month before and the month after. Remember that whole numbers represent dates and decimal numbers represent time in excel.

If in fact, that 4.6666667 = Jan 4, 1900, what you need to do, is format cell: Custom: "mmmm" for January, or "mmm" for "Jan"

If 4.666666 is meant to represent April 21, 1900, then you need to do the same format, but multiply 4.6666667 x 24.

Basically, we need to know what 4.6666667 represents on your work sheet before we can give you a formula and remember we also need to know what numbers before and after represent the month before and the month after!

Well, not knowing exactly how you want the results formatted, here's one
approach:

If the value is in cell A1 then, put this formula in any other cell:
=ROUNDDOWN(A1,0)&" years, "&ROUND(MOD(A1,1)*12,0)&" Months"

In your example, 4.66666 would display as "4 years, 8 months"

Does that help?

Regards,
Ron


"John" wrote:

> Hello.
>
> If I have a figure such as 4.6666667, that I would like expressed in
> Years and Months, to the nearest month, what formula would I use to do
> this? Is it possible in Excel?
>
> Cheers
>
> John
>
>
>