How to convert a number to years, months, days?

[Biz]

I am sure this is pretty simple, but I can't seem to figure it out. I have
this number that I want to convert in years, months, days format. What
formula to use to get the munber 505 converted to that. It is not the
difference between two dates, so I can't use datedif.
thanks.

 

[Tyro]

Format the number as a date. Dates are numbers is Excel. Jan 1, 1900 is day
1, Feb 10, 2008 is day 39,488, Dec 31, 9999 is day 2,958,465. So, 505
formatted as a date is 5/19/1901 - May 19, 1901.

Tyro

 

[Niek Otten]

So, what date is the number 505 supposed to mean? In other words, what is your date system?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I am sure this is pretty simple, but I can't seem to figure it out. I have
| this number that I want to convert in years, months, days format. What
| formula to use to get the munber 505 converted to that. It is not the
| difference between two dates, so I can't use datedif.
| thanks.

 

[T. Valko]

What result do you expect?

A year can have 365 or 366 days.

A month can have from 28 to 31 days.

 

[Vincent Robben]

Hi,

I guess your doing finance work? You really don't need to know the particular number of days for each month or year to have a formula giving you an average length of a period of time. I use the formula below to calculate an avarge length of stay for hospitals. The only draw back to my formula is it does months and days, and will not work for a period greater then a year. I am sure you might be able to figure something out or maybe someone here might be able to add to it. This formula will take a number such as 200 and turn it into months and days, and label both. A1 is the cell to referance. People repling need to understand we are talking about averages and not actual.

=INT(MOD(A1,365)/30)&" Months, "&ROUND(MOD((MOD(A1,365)),30),0)&" Days"