Use the EOMonth(startdate,#months) function for all your end dates. When you
have a partial month use 0 for the #months argument.

"OCD Cindy" wrote:

Thanks for the reply...sorry to be so unclear -

I am calculating lease rates within the term of a lease. For example my
columns a
A = Start Date
B = Term (in months or partial mos.)
C = End Date (the column I'm solving for)
D = Rental Rate
So, if there is a rent increase every year in a 10 year lease, I will have
approx. 10 rows. Each following row adds 1 day to the date calculated in
column C to begin the next rental rate increase period. The current formula
is working (though I don't quite understand it) EXCEPT when the initial term
is a partial month and needs to be calculated from a start date other than
the 1st. This happens often when the initial partial month is free and the
rental rate begins on the 1st of the following month. For example, free rent
from 8/15/06 - 8/31/06, and the new rate begins on 9/1/06. I've tried .5
(and several other decimals) as the term to get and end date 0f 8/31/06 for
that first period, but nothing works with the current formula.

Have I confused you even more?

"Duke Carey" wrote:

Even with the clarification, your problem still isn't too clear.

If you need to find the last day of the month, given a date within that
month, you can use

=eomonth(startdate,0) (requires the Analysis Toolpak add-in)

or

=date(year(startdate),month(startdate)+1,0)

this DOES NOT require the analysis toolpak
"OCD Cindy" wrote:

I guess I didn't explain the formula well. In my formula
(=IF(G870,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
G87 = term (in months)
C87 = start date

I'm using Excel XP.

Please help if you can!!!

"OCD Cindy" wrote:

I need a formula to calculate the end date of a monthly term when the start
date is not the 1st of the month. This is to show the proration for the
first month only, so the end date should be the last day of the initital
month/yr. Then when showing the rates for each period, all subsequent
periods would start on the 1st of the month. For example, start date
8/16/06, initial end date needs to be 8/31/06. The current forumla I am
using is:
=IF(G870,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
This works for all situations EXCEPT when the start date is other than the
1st and the term is less than 1 mo. (decimal).

Any clues???