Convert # of days to years/days

[Marlene Mayer]

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

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!!

[Ron Rosenfeld]

On Mon, 25 Apr 2005 21:13:33 -0600, Marlene Mayer <[email protected]>
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

[Marlene Mayer]

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 <[email protected]>
>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
>
>