+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Jason
    Guest

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

    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.

  2. #2
    Jason
    Guest

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

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

    "Jason" wrote:

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


  3. #3
    Ron Rosenfeld
    Guest

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

    On Sun, 20 Mar 2005 13:05:03 -0800, "Jason" <Jason@discussions.microsoft.com>
    wrote:

    >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

  4. #4
    Jason
    Guest

    Re: i need to add consecutive months and keep the day the same for

    Thanks Ron,

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

    "Ron Rosenfeld" wrote:

    > On Sun, 20 Mar 2005 13:05:03 -0800, "Jason" <Jason@discussions.microsoft.com>
    > wrote:
    >
    > >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
    >


  5. #5
    Ron Rosenfeld
    Guest

    Re: i need to add consecutive months and keep the day the same for

    On Sun, 20 Mar 2005 17:31:03 -0800, "Jason" <Jason@discussions.microsoft.com>
    wrote:

    >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

  6. #6
    Fred Smith
    Guest

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

    To add one month to a previous date, use:

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

    --
    Regards,
    Fred
    Please reply to newsgroup, not e-mail


    "Jason" <Jason@discussions.microsoft.com> wrote in message
    news:8BA67D72-8F0E-4C74-83CC-71D85BEAA5DC@microsoft.com...
    > Basically I need to be able to add 1 month to a previous date.
    >
    > "Jason" wrote:
    >
    >> 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.




  7. #7
    Jason
    Guest

    Re: i need to add consecutive months and keep the day the same for

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

    "Ron Rosenfeld" wrote:

    > On Sun, 20 Mar 2005 17:31:03 -0800, "Jason" <Jason@discussions.microsoft.com>
    > wrote:
    >
    > >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
    >


  8. #8
    Jason
    Guest

    Re: i need to add consecutive months and keep the day the same for

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

    "Ron Rosenfeld" wrote:

    > On Sun, 20 Mar 2005 17:31:03 -0800, "Jason" <Jason@discussions.microsoft.com>
    > wrote:
    >
    > >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
    >


  9. #9
    Ron Rosenfeld
    Guest

    Re: i need to add consecutive months and keep the day the same for

    On Mon, 21 Mar 2005 11:05:03 -0800, "Jason" <Jason@discussions.microsoft.com>
    wrote:

    >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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1