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 in Excel XP is:
=IF(G870,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
where G87 = term (in months or partial mos.)
and C87 is the start date.

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 EOMONTH instead of EDATE or without the Analysis ToolPak

DATE(YEAR(C87),MONTH(C87)+1,0)

will always give you the last day of the month of the date in C87


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"OCD Cindy" wrote in message
...
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 in Excel XP is:
=IF(G870,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
where G87 = term (in months or partial mos.)
and C87 is the start date.

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

See the answer to your earlier post.


Please don't post the same question multiple times.

"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 in Excel XP is:
=IF(G870,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
where G87 = term (in months or partial mos.)
and C87 is the start date.

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

sorry for the double post - I was trying to change the subject line... my
comments are all on the Lease Term post.

"Duke Carey" wrote:

See the answer to your earlier post.


Please don't post the same question multiple times.

"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 in Excel XP is:
=IF(G870,(EDATE(C87,G87)+(32-DAY(EDATE(C87,G87)-DAY(EDATE(A1,G87))+32))*MOD(G87,1)-1),"")
where G87 = term (in months or partial mos.)
and C87 is the start date.

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