i need to add consecutive months and keep the day the same for ea.

[Jason]

i'm working on an amortization spread sheet and i need to have the individual
due dates calculated the same way they are in the web examples.

 

[Jason]

Basically I need to be able to add 1 month to a previous date.

 

[Ron Rosenfeld]

i'm working on an amortization spread sheet and i need to have the individual
due dates calculated the same way they are in the web examples.

I don't know about the web example, but the only time there is a potential
problem is if the day of the month is >28.

The usual way is to add one month for each row, but to limit the maximum to the
last day of the month.

In order for this to work, you have to always reference back to the starting
date.

So, if StartDate is the date of Payment number one, then the formula:

=EDATE(StartDate,PmtNum-1)

Where PmtNum is the payment number (probably in a column next to the date).

If the EDATE function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


If you do not want to load the ATP, then the following formula will do the same
thing, but it's a bit longer :

=IF(DAY(DATE(YEAR(StartDate),MONTH(StartDate)+PmtNum-1,DAY(StartDate)))=DAY(StartDate),
DATE(YEAR(StartDate),MONTH(StartDate)+PmtNum-1,DAY(StartDate)),
DATE(YEAR(StartDate),MONTH(StartDate)+PmtNum-1,DAY(StartDate))-
DAY(DATE(YEAR(StartDate),MONTH(StartDate)+PmtNum-1,DAY(StartDate))))




--ron

 

[Jason]

Thanks Ron,

I'll give it a shot. i havent seen the "EDATE" function before so that may
do the trick

 

[Ron Rosenfeld]

I'll give it a shot. i havent seen the "EDATE" function before so that may
do the trick

Good luck. Let me know how it works out.
--ron

 

[Fred Smith]

To add one month to a previous date, use:

=date(year(a1),month(a1)+1,day(a1))

 

[Jason]

I'm still getting the "NAME?" error, even when I use the long formula you
provided, I'll either get it to install the addin or I'll take it home and
put the addin on my home computerand do it from there.

Either way, I think I'm on the right track. Can't thank you guy enough!!!

 

[Jason]

IT WORKS !!! IT REALLY WORKS!!!
Thanks fellas, couldn't have figured it out on my own

 

[Ron Rosenfeld]

IT WORKS !!! IT REALLY WORKS!!!
Thanks fellas, couldn't have figured it out on my own

Good to hear. Thanks for the feedback.

Best,

--ron