text: # Help!!! End of Month Calculation
  
  G
  
  #### Guest
  
  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 doesnâ€™t 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
  doesnâ€™t 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 doesnâ€™t 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
  
  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.)
  
  Maybe use the EOMONTH or DATE function to determine the last day of the month
  prior to the purchase date is and subtract it from B1. If the date in B1 is
  the end of whatever month you are looking at, you shouldn't need EOMONTH for
  B1 (unless your end date follows similar rules - you want a full month of
  depreciation for whatever date is entered in B1).
  
  =B1-EOMONTH(A1, -1)
  
  or
  =B1-DATE(YEAR(A1),MONTH(A1),0)
  
  or if you want to force B1 to the end of the month
  =EOMONTH(B1,0)-EOMONTH(A1, -1)
  
  or
  =DATE(YEAR(B1),MONTH(B1)+1,0)-DATE(YEAR(A1),MONTH(A1),0)
  
  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 doesn’t 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
  doesn’t 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 doesn’t 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
  
  I'm not sure what you are trying to do.
  
  If you want to obtain the number of months from end date to start date,
  inclusive, with a partial month counting as a full month for both the start and
  end dates, then:
  
  =DATEDIF(A1-DAY(A1),B1-DAY(B1),"m")+1
  
  should give you that count.
  
  A1 is the purchase date
  B1 is the current date.
  
  You might also want to take a look at the depreciation functions in Excel.
  
  Datedif is an undocumented excel function. See
  http://www.cpearson.com/excel.htm for some documentation.
  
  --ron
  
  Hi Tony
  
  If I understand you correctly, you want a full month's depreciation in
  month of acquisition, but only elapsed time for the final (or current
  month).
  If that is the case, then with dates in A1 and B1, Asset value in C1 and
  Depreciation rate in D1
  =(B1-DATE(YEAR(A1),MONTH(A1),0))/365.25*C1*D1