Year-Days-Months

[Guest]

If I have 13346 days, I need to find out how many years, months & days that
represents. I got as far as 13346 / 365 = 36.56, so I could get the years
with left,2, but I'm having trouble converting the .56 into a 30 day month,
and the # of days remaining.
Any help would be greatly appreciated.

Thanks,

Steve

 

[Biff]

Hi!

Are you sure you want to use a 30 day month with a 365 day year?

If so, what result would you expect from 364 days?

Biff

 

[Roger Govier]

Hi Steve

One way would be to create 2 absolute dates then use the Datedif
function.
Put a starting date of say 01/01/2000 in a cell - I used C8
In cell D8 enter
=C8+13346
Then use the following formula
=DATEDIF(C8,D8,"y") &" years "&DATEDIF(C8,D8,"ym")&" months
"&DATEDIF(C8,D8,"md")&"days"

which returned 36 years 6 months 15 days for me.

 

[Guest]

Perfect !! Thanks so much. It came out to be an exact match of what I needed.
Now with the today(), &/or various future dates manually entered, your
formula will enable me to figure out when I'm going to retire.

Thanks again,

Steve

 

[Guest]

I didn't acoount for that, and you're right, it could potentially produce a
variance of many days. But, See Roger's solution below.

Thanks again,

Steve

 

[Guest]

One more small problem. E.g. , if the prior calcs resulted in 20 years, 14
months,I can figure out if months are = > 12, to add a 1 to the years with
this formula
Year cell =IF(I4>=12,H4+1,I4) &
Month cell =(IF(I4>=12,I4-12,I4))
Yrs Months
H4 I4
20 14

21 2

This will work up to 24 months, but how could I get it to work for >24
months ?

Much appreciated,

Thanks,

Steve

 

[Roger Govier]

Hi Steve

You're very welcome, thanks for the feedback.
I wish my retirement were that far ahead!!!

 

[Guest]

Not sure you saw this, but...

One more small problem. E.g. , if the prior calcs resulted in 20 years, 14
months,I can figure out if months are = > 12, to add a 1 to the years with
this formula
Year cell =IF(I4>=12,H4+1,I4) &
Month cell =(IF(I4>=12,I4-12,I4))
Yrs Months
H4 I4
20 14

21 2

This will work up to 24 months, but how could I get it to work for >24
months ?

Much appreciated,

Thanks,

Steve

 

[Roger Govier]

Hi Steve

I cannot see how the Datedif solution I provided will return a value
greater than 12 months.
=DATEDIF(C8,D8,"y")
would return just the integer of the years and returns a result of 36
=DATEDIF(C8,D8,"m")
would return the result in the integer of the months only, but in the
case of the data originally
provided would return a result of 438
=DATEDIF(C8,D8,"ym")
(as provided) would return the integer of the months, having excluded
the years (and years*12 months) from the calculation and therefore
returns a result of 6
=DATEDIF(C8,D8,"d")
would return the total days and would return our starting value of 13346
days
=DATEDIF(C8,D8,"md")
would return the number of days after excluding all months (and
months*month length) from the calculation and returns 15

To answer your query more generally assuming you are not using Datedif,
then

Year cell =IF(I4>=12,H4+INT(I4/12),H4) (you had erroneously
repeated I4 rather than H4 in your original posting)

Month cell =(IF(I4>=12,MOD(I4,12),I4))

 

[Guest]

Again, a perfect solution. Thanks so much.
I did use you original dateif solution, but I had to put each of the y, ym,
& md in separate cells, because I also had to add to the original Year, month
& days other years, months and days from a different catagory, and had to add
both together. That's where my problem came in, e.g. if the original dateif
for months returned 9 and the extra month calc returned 6, I needed the 15
months to return an additional year, with the 3 months remainder. I'm
probably not explaining it very well, but again, both of your solutions
worked great for what I wanted to do.
Again, very many thanks.

Steve

 

[Roger Govier]

Hi Steve
Thanks for the feedback.
I understand fully what you are saying and understand why you needed the
additional formulae.