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???

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???

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???

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???

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???

Thanks again for the help. That works in the scenario where I want to use a
partial term to arrive at an end date of the end of the first month, but
unfortunately that is not always the case. Sometimes a term can begin on
8/23/06 and increase every year on the anniversary date (instead of the 1st).
Is there something else I could try that would work in both scenarios?

"Duke Carey" wrote:

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???