On Mon, 25 Apr 2005 21:13:33 -0600, Marlene Mayer
wrote:

I'm having a bit of a problem with a format that I think should work,
but isn't. I'm trying to convert the total # of days to years/days.
For example, in Column A1, I have 425 as the # of days. I've formatted
Column B with a custom format of yy "Years" dd "Days". For 425 days,
it should end up giving me a result of approximately 1 Year 60 Days,
however it is returning a result of 1 year 28 days. Any assistance that
can be offered with this would be greatly appreciated, thanks!!

You cannot do that by formatting. When you format a cell as a date, Excel
interprets the result as a date with day 1 = 1/1/1900 (or 1/1/1904 depending on
the date setting). So your format is giving the year and day of whatever date
is 425 days from the start of the date system. In your case it is giving the
year and day of the date 28 Feb 1901.

To convert your 425 into years & days, you first have to adopt a convention for
how many days in a year, since it can differ.

One simple way would be to assume 365.25 days in a year, and that you want to
round the fractional days:

=INT(A1/365.25) & " Years " &
ROUND(MOD(A1/365.25,1)*365.25,0) & " Days"


--ron