software

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(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
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(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
G87 = term (in months)
C87 = start date

I'm using Excel XP.

Please help if you can!!!

quot;OCD Cindyquot; wrote:

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

gt; I guess I didn't explain the formula well. In my formula
gt; (=IF(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
gt; G87 = term (in months)
gt; C87 = start date
gt;
gt; I'm using Excel XP.
gt;
gt; Please help if you can!!!
gt;
gt; quot;OCD Cindyquot; wrote:
gt;
gt; gt; I need a formula to calculate the end date of a monthly term when the start
gt; gt; date is not the 1st of the month. This is to show the proration for the
gt; gt; first month only, so the end date should be the last day of the initital
gt; gt; month/yr. Then when showing the rates for each period, all subsequent
gt; gt; periods would start on the 1st of the month. For example, start date
gt; gt; 8/16/06, initial end date needs to be 8/31/06. The current forumla I am
gt; gt; using is:
gt; gt; =IF(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
gt; gt; This works for all situations EXCEPT when the start date is other than the
gt; gt; 1st and the term is less than 1 mo. (decimal).
gt; gt;
gt; gt; 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?

quot;Duke Careyquot; wrote:

gt; Even with the clarification, your problem still isn't too clear.
gt;
gt; If you need to find the last day of the month, given a date within that
gt; month, you can use
gt;
gt; =eomonth(startdate,0) (requires the Analysis Toolpak add-in)
gt;
gt; or
gt;
gt; =date(year(startdate),month(startdate) 1,0)
gt;
gt; this DOES NOT require the analysis toolpak
gt; quot;OCD Cindyquot; wrote:
gt;
gt; gt; I guess I didn't explain the formula well. In my formula
gt; gt; (=IF(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
gt; gt; G87 = term (in months)
gt; gt; C87 = start date
gt; gt;
gt; gt; I'm using Excel XP.
gt; gt;
gt; gt; Please help if you can!!!
gt; gt;
gt; gt; quot;OCD Cindyquot; wrote:
gt; gt;
gt; gt; gt; I need a formula to calculate the end date of a monthly term when the start
gt; gt; gt; date is not the 1st of the month. This is to show the proration for the
gt; gt; gt; first month only, so the end date should be the last day of the initital
gt; gt; gt; month/yr. Then when showing the rates for each period, all subsequent
gt; gt; gt; periods would start on the 1st of the month. For example, start date
gt; gt; gt; 8/16/06, initial end date needs to be 8/31/06. The current forumla I am
gt; gt; gt; using is:
gt; gt; gt; =IF(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
gt; gt; gt; This works for all situations EXCEPT when the start date is other than the
gt; gt; gt; 1st and the term is less than 1 mo. (decimal).
gt; gt; gt;
gt; gt; gt; 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.

quot;OCD Cindyquot; wrote:

gt; Thanks for the reply...sorry to be so unclear -
gt;
gt; I am calculating lease rates within the term of a lease. For example my
gt; columns a
gt; A = Start Date
gt; B = Term (in months or partial mos.)
gt; C = End Date (the column I'm solving for)
gt; D = Rental Rate
gt; So, if there is a rent increase every year in a 10 year lease, I will have
gt; approx. 10 rows. Each following row adds 1 day to the date calculated in
gt; column C to begin the next rental rate increase period. The current formula
gt; is working (though I don't quite understand it) EXCEPT when the initial term
gt; is a partial month and needs to be calculated from a start date other than
gt; the 1st. This happens often when the initial partial month is free and the
gt; rental rate begins on the 1st of the following month. For example, free rent
gt; from 8/15/06 - 8/31/06, and the new rate begins on 9/1/06. I've tried .5
gt; (and several other decimals) as the term to get and end date 0f 8/31/06 for
gt; that first period, but nothing works with the current formula.
gt;
gt; Have I confused you even more?
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; Even with the clarification, your problem still isn't too clear.
gt; gt;
gt; gt; If you need to find the last day of the month, given a date within that
gt; gt; month, you can use
gt; gt;
gt; gt; =eomonth(startdate,0) (requires the Analysis Toolpak add-in)
gt; gt;
gt; gt; or
gt; gt;
gt; gt; =date(year(startdate),month(startdate) 1,0)
gt; gt;
gt; gt; this DOES NOT require the analysis toolpak
gt; gt; quot;OCD Cindyquot; wrote:
gt; gt;
gt; gt; gt; I guess I didn't explain the formula well. In my formula
gt; gt; gt; (=IF(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
gt; gt; gt; G87 = term (in months)
gt; gt; gt; C87 = start date
gt; gt; gt;
gt; gt; gt; I'm using Excel XP.
gt; gt; gt;
gt; gt; gt; Please help if you can!!!
gt; gt; gt;
gt; gt; gt; quot;OCD Cindyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I need a formula to calculate the end date of a monthly term when the start
gt; gt; gt; gt; date is not the 1st of the month. This is to show the proration for the
gt; gt; gt; gt; first month only, so the end date should be the last day of the initital
gt; gt; gt; gt; month/yr. Then when showing the rates for each period, all subsequent
gt; gt; gt; gt; periods would start on the 1st of the month. For example, start date
gt; gt; gt; gt; 8/16/06, initial end date needs to be 8/31/06. The current forumla I am
gt; gt; gt; gt; using is:
gt; gt; gt; gt; =IF(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
gt; gt; gt; gt; This works for all situations EXCEPT when the start date is other than the
gt; gt; gt; gt; 1st and the term is less than 1 mo. (decimal).
gt; gt; gt; gt;
gt; gt; gt; gt; 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?

quot;Duke Careyquot; wrote:

gt; Use the EOMonth(startdate,#months) function for all your end dates. When you
gt; have a partial month use 0 for the #months argument.
gt;
gt; quot;OCD Cindyquot; wrote:
gt;
gt; gt; Thanks for the reply...sorry to be so unclear -
gt; gt;
gt; gt; I am calculating lease rates within the term of a lease. For example my
gt; gt; columns a
gt; gt; A = Start Date
gt; gt; B = Term (in months or partial mos.)
gt; gt; C = End Date (the column I'm solving for)
gt; gt; D = Rental Rate
gt; gt; So, if there is a rent increase every year in a 10 year lease, I will have
gt; gt; approx. 10 rows. Each following row adds 1 day to the date calculated in
gt; gt; column C to begin the next rental rate increase period. The current formula
gt; gt; is working (though I don't quite understand it) EXCEPT when the initial term
gt; gt; is a partial month and needs to be calculated from a start date other than
gt; gt; the 1st. This happens often when the initial partial month is free and the
gt; gt; rental rate begins on the 1st of the following month. For example, free rent
gt; gt; from 8/15/06 - 8/31/06, and the new rate begins on 9/1/06. I've tried .5
gt; gt; (and several other decimals) as the term to get and end date 0f 8/31/06 for
gt; gt; that first period, but nothing works with the current formula.
gt; gt;
gt; gt; Have I confused you even more?
gt; gt;
gt; gt; quot;Duke Careyquot; wrote:
gt; gt;
gt; gt; gt; Even with the clarification, your problem still isn't too clear.
gt; gt; gt;
gt; gt; gt; If you need to find the last day of the month, given a date within that
gt; gt; gt; month, you can use
gt; gt; gt;
gt; gt; gt; =eomonth(startdate,0) (requires the Analysis Toolpak add-in)
gt; gt; gt;
gt; gt; gt; or
gt; gt; gt;
gt; gt; gt; =date(year(startdate),month(startdate) 1,0)
gt; gt; gt;
gt; gt; gt; this DOES NOT require the analysis toolpak
gt; gt; gt; quot;OCD Cindyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I guess I didn't explain the formula well. In my formula
gt; gt; gt; gt; (=IF(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
gt; gt; gt; gt; G87 = term (in months)
gt; gt; gt; gt; C87 = start date
gt; gt; gt; gt;
gt; gt; gt; gt; I'm using Excel XP.
gt; gt; gt; gt;
gt; gt; gt; gt; Please help if you can!!!
gt; gt; gt; gt;
gt; gt; gt; gt; quot;OCD Cindyquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I need a formula to calculate the end date of a monthly term when the start
gt; gt; gt; gt; gt; date is not the 1st of the month. This is to show the proration for the
gt; gt; gt; gt; gt; first month only, so the end date should be the last day of the initital
gt; gt; gt; gt; gt; month/yr. Then when showing the rates for each period, all subsequent
gt; gt; gt; gt; gt; periods would start on the 1st of the month. For example, start date
gt; gt; gt; gt; gt; 8/16/06, initial end date needs to be 8/31/06. The current forumla I am
gt; gt; gt; gt; gt; using is:
gt; gt; gt; gt; gt; =IF(G87gt;0,(EDATE(C87,G87) (32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87)) 32))*MOD(G87,1)-1),quot;quot;)
gt; gt; gt; gt; gt; This works for all situations EXCEPT when the start date is other than the
gt; gt; gt; gt; gt; 1st and the term is less than 1 mo. (decimal).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Any clues???

Any such formula would have to rely on one item of information that you don't
mention - how do you determine whether to use the anniversary date or the end
of a month? Excel doesn't have a telepath feature, so you'll have to figure
out some way to store an indicator.

If the indicator is quot;EOMquot; (for End Of Month), then use the EOMonth()
function, otherwise use the EDATE() function

=IF(indicator cell=quot;EOMquot;,eomonth(startdate,#months),edate(startd ate,#months))

quot;OCD Cindyquot; wrote:

gt; Thanks again for the help. That works in the scenario where I want to use a
gt; partial term to arrive at an end date of the end of the first month, but
gt; unfortunately that is not always the case. Sometimes a term can begin on
gt; 8/23/06 and increase every year on the anniversary date (instead of the 1st).
gt; Is there something else I could try that would work in both scenarios?The determining factor is the Lease Document itself. The document I'm
creating is an abstract of the lease. What I want to be able to do is input
a lease start date, then the # of months or partial months until the next
incremental increase to arrive at an end date for that rate. I don't quite
understand why the formula I'm using isn't working in just this one
situation. I can input a start date of the 1st of any month and use a
decimal (partial month) as the term and the end date will calculate
correctly. The only scenario where this does not work is when I combine a
partial month term with a start date of other than the first.

I wouldn't nit-pick this, but it is a standardized form with all the
formulas protected to keep people in the field from changing the format.

Do you know of any way to do the following calculation which would cover all
scenarios?:

Start Date (MM/DD/YY) Term (# mos./partial mos.) = End
Date (MM/DD/YY)

quot;Duke Careyquot; wrote:

gt; Any such formula would have to rely on one item of information that you don't
gt; mention - how do you determine whether to use the anniversary date or the end
gt; of a month? Excel doesn't have a telepath feature, so you'll have to figure
gt; out some way to store an indicator.
gt;
gt; If the indicator is quot;EOMquot; (for End Of Month), then use the EOMonth()
gt; function, otherwise use the EDATE() function
gt;
gt; =IF(indicator cell=quot;EOMquot;,eomonth(startdate,#months),edate(startd ate,#months))
gt;
gt; quot;OCD Cindyquot; wrote:
gt;
gt; gt; Thanks again for the help. That works in the scenario where I want to use a
gt; gt; partial term to arrive at an end date of the end of the first month, but
gt; gt; unfortunately that is not always the case. Sometimes a term can begin on
gt; gt; 8/23/06 and increase every year on the anniversary date (instead of the 1st).
gt; gt; Is there something else I could try that would work in both scenarios?
gt;

If the ONLY time you use a partial month is when you need a month-end date,
then modify the formula I gave you earlier to

=IF(mod(#months,1)gt;0,eomonth(startdate,#months),ed ate(startdate,#months))
quot;OCD Cindyquot; wrote:

gt; The determining factor is the Lease Document itself. The document I'm
gt; creating is an abstract of the lease. What I want to be able to do is input
gt; a lease start date, then the # of months or partial months until the next
gt; incremental increase to arrive at an end date for that rate. I don't quite
gt; understand why the formula I'm using isn't working in just this one
gt; situation. I can input a start date of the 1st of any month and use a
gt; decimal (partial month) as the term and the end date will calculate
gt; correctly. The only scenario where this does not work is when I combine a
gt; partial month term with a start date of other than the first.
gt;
gt; I wouldn't nit-pick this, but it is a standardized form with all the
gt; formulas protected to keep people in the field from changing the format.
gt;
gt; Do you know of any way to do the following calculation which would cover all
gt; scenarios?:
gt;
gt; Start Date (MM/DD/YY) Term (# mos./partial mos.) = End
gt; Date (MM/DD/YY)
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; Any such formula would have to rely on one item of information that you don't
gt; gt; mention - how do you determine whether to use the anniversary date or the end
gt; gt; of a month? Excel doesn't have a telepath feature, so you'll have to figure
gt; gt; out some way to store an indicator.
gt; gt;
gt; gt; If the indicator is quot;EOMquot; (for End Of Month), then use the EOMonth()
gt; gt; function, otherwise use the EDATE() function
gt; gt;
gt; gt; =IF(indicator cell=quot;EOMquot;,eomonth(startdate,#months),edate(startd ate,#months))
gt; gt;
gt; gt; quot;OCD Cindyquot; wrote:
gt; gt;
gt; gt; gt; Thanks again for the help. That works in the scenario where I want to use a
gt; gt; gt; partial term to arrive at an end date of the end of the first month, but
gt; gt; gt; unfortunately that is not always the case. Sometimes a term can begin on
gt; gt; gt; 8/23/06 and increase every year on the anniversary date (instead of the 1st).
gt; gt; gt; Is there something else I could try that would work in both scenarios?
gt; gt;

Unfortunately, sometimes I don't need the month-end date when using a partial
month. It just depends on how the lease is written. I just want it to
calculate correrctly based on the term I use. It works fine if, for example,
the lease start date is 3/1/06 and the first 1/2 mo. is free, I would put a
term of .5 mos. and the end date for that period would be 3/15/06. The
partial month straight calculation only doesn't work if the start date is
other than the first. And some times I might have a start date of 3/15/06
and a term of 60 months ending 3/14/11, so it needs to be able to calculate
either way. Hmm...

quot;Duke Careyquot; wrote:

gt; If the ONLY time you use a partial month is when you need a month-end date,
gt; then modify the formula I gave you earlier to
gt;
gt; =IF(mod(#months,1)gt;0,eomonth(startdate,#months),ed ate(startdate,#months))
gt;
gt;
gt;
gt; quot;OCD Cindyquot; wrote:
gt;
gt; gt; The determining factor is the Lease Document itself. The document I'm
gt; gt; creating is an abstract of the lease. What I want to be able to do is input
gt; gt; a lease start date, then the # of months or partial months until the next
gt; gt; incremental increase to arrive at an end date for that rate. I don't quite
gt; gt; understand why the formula I'm using isn't working in just this one
gt; gt; situation. I can input a start date of the 1st of any month and use a
gt; gt; decimal (partial month) as the term and the end date will calculate
gt; gt; correctly. The only scenario where this does not work is when I combine a
gt; gt; partial month term with a start date of other than the first.
gt; gt;
gt; gt; I wouldn't nit-pick this, but it is a standardized form with all the
gt; gt; formulas protected to keep people in the field from changing the format.
gt; gt;
gt; gt; Do you know of any way to do the following calculation which would cover all
gt; gt; scenarios?:
gt; gt;
gt; gt; Start Date (MM/DD/YY) Term (# mos./partial mos.) = End
gt; gt; Date (MM/DD/YY)
gt; gt;
gt; gt; quot;Duke Careyquot; wrote:
gt; gt;
gt; gt; gt; Any such formula would have to rely on one item of information that you don't
gt; gt; gt; mention - how do you determine whether to use the anniversary date or the end
gt; gt; gt; of a month? Excel doesn't have a telepath feature, so you'll have to figure
gt; gt; gt; out some way to store an indicator.
gt; gt; gt;
gt; gt; gt; If the indicator is quot;EOMquot; (for End Of Month), then use the EOMonth()
gt; gt; gt; function, otherwise use the EDATE() function
gt; gt; gt;
gt; gt; gt; =IF(indicator cell=quot;EOMquot;,eomonth(startdate,#months),edate(startd ate,#months))
gt; gt; gt;
gt; gt; gt; quot;OCD Cindyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Thanks again for the help. That works in the scenario where I want to use a
gt; gt; gt; gt; partial term to arrive at an end date of the end of the first month, but
gt; gt; gt; gt; unfortunately that is not always the case. Sometimes a term can begin on
gt; gt; gt; gt; 8/23/06 and increase every year on the anniversary date (instead of the 1st).
gt; gt; gt; gt; Is there something else I could try that would work in both scenarios?
gt; gt; gt;