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