Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Senior Member
Join Date
8th July 2006
Location
San Diego
Posts
215

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

VB:
```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?

Excel Video Tutorials / Excel Dashboards Reports

2. Join Date
18th September 2005
Location
Hampshire, UK
Posts
1,278

## Re: Adding Exactly One Month To A Date

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

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
23rd April 2008
Location
Manchester, UK
Posts
12

## Re: Adding Exactly One Month To A Date

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

Excel Video Tutorials / Excel Dashboards Reports

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

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

5. Senior Member
Join Date
8th July 2006
Location
San Diego
Posts
215

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

Excel Video Tutorials / Excel Dashboards Reports

6. Six Strings Guest

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

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Add One Month To Date

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

```
Or:
VB:
```=EDATE(A1,-1) 'To decrease by exactly 1 month

```

8. I agreed to these rules
Join Date
29th November 2010
Posts
1

## 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 at 21:33. Reason: delete quote

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Add One Month To Date

Hello MikBed,

Welcome to Ozgrid.

You could send a Private Message.

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

#### Posting Permissions

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