Depreciation calculation based on date and if functions

[mingali]

Hi,

In my formula for Col AD to AO, i calculate my amount based on date. It works for some and not for others. For example, for AD8 and AD9, if my date in AD5 change to 1/1/2013. the formula will become 0.01. but if it is 31/1/2013, it will show the correct answer. however, for 8 Jan 2013 is greater than 1 Jan 2013, shouldnt it represent the same answer?

Also, when AD5 is 31/1/2013, cell AQ8 and AQ9, will show the wrong answers.


I am tired and confused now. Can someone help please? I may not be explain clearly as i am a bit disoriented. I will clarify if got any query.

Thanks

[daffodil11]

Your formula compares the date in D8 to AD5, not just the month.

=IF(AD$4="Actual",IFERROR(ROUND(IF(AND(RawData!$C8="disposal",MONTH(AE$5)=MONTH($D8),YEAR(AE$5)=YEAR($D8)),-#REF!-#REF!,IF(AD$5<=RawData!$D8,0,IF(AND(AD$5>=$T8,$T8<>0),0,IF($AQ8>(($Q8-$S8)/($R8*12)),(($Q8-$S8)/($R8*12)),IF($AQ8=1,0,IF($AQ8<0,(($Q8-$S8)/($R8*12)),$AQ8-$S8)))))),2),0),0)+0.01


If you make AD5 > D8, the formula changes.

[mingali]

if i made as you suggested, it will affect other cells' answers. ie, other answers will be incorrect.

I need to compare the date. but the answer is wrong. 8 Jan 2013 is greater than 1 Jan 2013. but how come the result is nil instead of the correct figures?

[daffodil11]

I wasn't suggesting you make a change. I'm pointing out that your formula compares the dates, not the months.