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!!

Hi!

The reason you're getting 1 year 28 days is because 425 is the date serial
number for Feb 28 1901.

This may be a little overkill but it does what you want and assumes that ALL
years are 365 days:

=INT(A1/365)&IF(INT(A1/365)=1,"Year ","Years
")&IF(MOD(A1,365)=1,MOD(A1,365)&" Day",MOD(A1,365)&" Days")

Biff

"Marlene Mayer" wrote in message
...
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!!

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

Thanks to both Biff & Ron, that's exactly what I was looking for!!

Ron Rosenfeld wrote:

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