No announcement yet.

Unconfigured Ad Widget


Add One Month To Date

This topic is closed.
  • Filter
  • Time
  • Show
Clear All
new posts

  • Add One Month To Date

    Ok I think this is a really silly problem... Is there a way to add 1 month minus 1 day to a given date? With the exceptions below:

    Let d m and y be the day month and year respectively.

    new_m = m+1
    new_d = d-1
    if m = 12 then new_m = 1 and new_y = y + 1
    if d = 1 then
      new_d = 31
      if m = 4,6,9,11 then new_d = 30
      if m = 2 and y mod 4 = 0 then new_d = 29
      if m = 2 and y mod 4 <> 0 then new_d = 28
    end if
    if d = 31 then
      new_d = 30
      if m = 4,6,9,11 then new_d = 29
    'etc etc
    'then new_d is 28 and 27 for february, and then i have to make exceptino cases for when d is 28 29 30 as well...
    Is there a simpler way to do this? Or perhaps some built in function?

  • #2
    Re: Adding Exactly One Month To A Date


    If you set a reference to atpvbaen.xls (ie the Analysis Toolpak) in the VBE via Tools>References then you can use the Edate function.



    • #3
      Re: Adding Exactly One Month To A Date

      If you just want a formula, this one works for me:


      where the original date is in cell A1 and the format required is "dd/mm/yyyy".


      • #4
        Re: Adding Exactly One Month To A Date

        Exactly One Month
        Define "Exactly One Month". 31, 30, 29 or 28 days?


        • #5
          Re: Add One Month To Date

          I suppose that is part of my problem, defining "exactly one month"
          In general, if I start on any given day, then the "month" will last until that day - 1.
          So if i start on 1/13 then the end of that "month" will be 2/12.

          However, if i started say on 1/30 then I want the "months" to be as followed:
          1/30-2/27 (assuming no leap year), 2/28-3/29, 3/30-4/29, etc.
          If i started on 1/31, then:
          1/31-2/27, 2/28-3/30, 3/31-4/29, 4/30-5/30, etc.

          I assume this really doesn't make much sense... so I just ended up coding lots of if/then statements to get all the exceptions cases the way i needed it to be. It actually wasn't as much as I thought it would be... but if there is some built in function that does this (assuming people could actually follow my logic on what a "month" is o.O) i'd still be curious to know!

          Formula by peanut gives 3/1 when adding 1 month minus 1 day to 1/31.
          RichardSchollar - I don't have "atpvbaen.xls (ie the Analysis Toolpak)" where is that from?


          • #6
            Re: Add One Month To Date

            Your algorithm is not well defined on paper, so your problem is not a coding problem. From what I can gather in your examples, you want this:

            If the current day minus one is available in the following month, use that day
            ' example: 1/15 to 2/14, 1/29 to 2/28

            Else count the number of days remaining in the month after the given day and add 1, and subtract that number from the last day in the following month (last day of the month must take into account leap year for Feb)
            ' examples 1/31 to 2/27 (not leap year), 2/28 to 3/30 (not leap year), 4/30 to 5/30

            End If

            However, note that using this description, you have 1/29 to 2/28, but starting on a later date of 1/31 lands you on an earlier date of 2/27 because you're now counting from the end of the month. This may be an unintended consequence, and I can't tell if you want that to happen, or not.

            My suggestion is that you first write a concise English (or whatever) description of what you want. Once that is clear in your head, it will be easy to code it.


            • #7
              Re: Add One Month To Date

              Assuming your date is in cell A1:
              =EDATE(A1,1) 'To increase by exactly 1 month
              =EDATE(A1,-1) 'To decrease by exactly 1 month



              • #8
                Re: Add One Month To Date

                Apologize if this bumps an old thread. I have nothing to add.

                But perhaps someone more experienced can tell me how I otherwise can say thank you to Mattheq for this tip. Simple, but it worked, and it was important for me to have it solved.
                Last edited by AAE; November 29th, 2010, 21:33. Reason: delete quote


                • #9
                  Re: Add One Month To Date

                  Hello MikBed,

                  Welcome to Ozgrid.

                  You could send a Private Message.

                  Forum Rules | Message to Cross Posters | How to use Tags




                  There are no results that meet this criteria.