Convert # of days to years/days

M

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

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
 
R

Ron Rosenfeld

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top