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

[Toppers]

Try:

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

HTH

"naughtyboy" wrote:

>
> 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
>
>
> --
> naughtyboy
> ------------------------------------------------------------------------
> naughtyboy's Profile: http://www.excelforum.com/member.php...o&userid=37151
> View this thread: http://www.excelforum.com/showthread...hreadid=573395
>
>

[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

Carl

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

[Toppers]

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.

"mr_teacher" wrote:

>
> 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
>
> Carl
> naughtyboy Wrote:
> > 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
>
>
> --
> mr_teacher
> ------------------------------------------------------------------------
> mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
> View this thread: http://www.excelforum.com/showthread...hreadid=573395
>
>