| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAn any one solve this problem of days months and years.its urgent plzzzzzzzzzzzz
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CAn any one solve this problem of days months and years.its urgent
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CAn any one solve this problem of days months and years.its urgent plzzzzzzzzzzzz
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(B211,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(B211,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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
CAn any one solve this problem of days months and years.its ur
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(B211,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(B211,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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
