CAn any one solve this problem of days months and years.its urgent plzzzzzzzzzzzz

[naughtyboy]

Hi friends..i have a problem in ecel. supose i have the following excel
sheet

A2 = Years = 0
B2 = Months = 47
C2 = Days = 95
i ma ssuming 365 days in a year and 30 days in a month..
now i want to convert these days and months into years months and days
as answer should be 4 years 2 months and 5 days i want a formula to
calculate the answer.....
i am using the following formulas for this problem.
Years: =A2+INT(B2/12)
Just in case the days >=360 change to:

=A2+INT(B2/12)+INT(C2/360)

Months: =MOD(B2+INT(C2/30),12)

Days: =MOD(C2,30)
but these formulas give me answer as 3 years 2 months and 5 days.as it
is wrong it should be 4 years 2months and 5 days..


i will be evry thank ful to u.plz do help meeeeeeeeee

 

[Guest]

Try:

Years:
=A2+ INT((B2*30+C2)/360)
Months:
=INT(MOD((B2*30+C2),360)/30)
Days:
=MOD(B2*30+C2,30)

HTH

 

[mr_teacher]

Doesn't making the assumptions on the length of the month cause problems
in calculations? Playing around with this a little I thought that this
should be an answer of 4 years, 2 months and 3 days.

This was using the formulas of
Years :
=A2+IF(B2>11,ROUNDDOWN(B2/12,0))+ROUNDDOWN((($B$2-(ROUNDDOWN($B$2/12,0)*12))+ROUNDDOWN($C$2/(365/12),0))/12,0)
Months :
=(((($B$2-(ROUNDDOWN($B$2/12,0)*12))+ROUNDDOWN($C$2/(365.25/12),0)))/12-ROUNDDOWN(((($B$2-(ROUNDDOWN($B$2/12,0)*12))+ROUNDDOWN($C$2/(365.25/12),0)))/12,0))*12
Days : =ROUNDDOWN(C2-(ROUNDDOWN(C2/(365.25/12),0))*(365.25/12),0)

Or alternatively using the formula

=A2+IF(B2>11,ROUNDDOWN(B2/12,0))+ROUNDDOWN((($B$2-(ROUNDDOWN($B$2/12,0)*12))+ROUNDDOWN($C$2/(365/12),0))/12,0)&"
Years
"&(((($B$2-(ROUNDDOWN($B$2/12,0)*12))+ROUNDDOWN($C$2/(365.25/12),0)))/12-ROUNDDOWN(((($B$2-(ROUNDDOWN($B$2/12,0)*12))+ROUNDDOWN($C$2/(365.25/12),0)))/12,0))*12&"
Months "&ROUNDDOWN(C2-(ROUNDDOWN(C2/(365.25/12),0))*(365.25/12),0)&"
Days"

Or is this just me being obsessed with trying to find different ways
for doing things? Your way does seem much neater!!!

Regards

 

[Guest]

There is a contradiction between 365 days in a year and the 30 days per
month. As the OP appeared to basing the calculation on 360, I worked on that
basis but it is not a "pure" solution.

Your formulas are probably a better (more correct) answer.