Add One Month To Date

[farful]

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 
 
 'EXCEPTIONS:
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?

[RichardSchollar]

Hi

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.

Richard

[peanut1999]

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

=TEXT(DATE(YEAR(A1),MONTH(A1)-1,DAY(A1)-1),"dd/mm/yyyy")

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

[Dave Hawley]

Exactly One Month

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

[farful]

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?

[Six Strings]

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.

[Mattheq]

Assuming your date is in cell A1:

=EDATE(A1,1) 'To increase by exactly 1 month

Or:

=EDATE(A1,-1) 'To decrease by exactly 1 month

[MikBed]

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.

[AAE]

Hello MikBed,

Welcome to Ozgrid.

You could send a Private Message.