I'm inferring that column A will be your purchase date, which may be any day
of any month, and that column B will be the date for which you want to
calculate the depreciation, and will always be the last day of the month and
no earlier than the purchase date. I think I'd just adjust the purchase date
back to the last day of the PRIOR month, and use the ROUND function to
convert the number of elapsed days to the number of elapsed months.
=ROUND((B1-(A1-DAY(A1)))/(365.25/12),0)
(If you want to stick with calendar functions, don't just use
month(b1)-month(a1), but rather
(month(b1)+12*(year(b1))-(month(a1)+12*year(a1))). Then when December ends,
the number of elapsed months will increase by 1 instead of decreasing by 11
as you've got now.)


"Tony" wrote:

I need some help. I am writing a formula to calculate my asset depreciation
schedule. The rule is, the new purchased asset will be depreciated for the
full in current month, no matter what date the asset purchased within the
month, the depreciation will be calculated for full month e.g. purchase on
29-Nov-06 will depreciate for 30 days in Nov.

I use EOMONTH function, it works in purchase month (refer Purchase Month
table 1). However, it doesnt work afterward, because EOMONTH calculated in
days (Refer After Purchase table 1). Then I changed into Month format. It
works in after purchase period (Refer After Purchase table 2) but then it
doesnt work in purchase month (Refer Purchase Month 2). I changed MONTH &
EOMONTH combine, it works in purchase month (Refer Purchase Month table 3)
but it doesnt work in after purchase (Refer After Purchase table 3).


Purchase Month
A1 B1 Formula Result
1 29-Nov-06 30-Nov-06 =EOMONTH(B1,0)-EOMONTH(A1,0) 0
2 29-Nov-06 30-Nov-06 =month((EOMONTH(B1,0)-EOMONTH(A1,0))) 1
3 29-Nov-06 30-Nov-06 =MONTH(EOMONTH(B1,0))-MONTH(EOMONTH(A1,0)) 0

After Purchase
A1 B1 Formula Result
1 29-Nov-06 30-Jun-07 =EOMONTH(B1,0)-EOMONTH(A1,0) 212
2 29-Nov-06 30-Jun-07 =month((EOMONTH(B1,0)-EOMONTH(A1,0))) 7
3 29-Nov-06 30-Jun-07 =MONTH(EOMONTH(B1,0))-MONTH(EOMONTH(A1,0)) -5