Adding a month to a date ??

[Brad]

Hi Guys,
I am using excel and I need to do something that should be simple but seems to be hard grrrrrrrr..

Ok I want to take a date and simple add a month to it ??

Any ideas ???

I am sure this is easy but need help ..

 

[john471]

Adding a month is not necessarily "simple"!

e.g. what is
29 JAN 2007 + 1 month = ??
29 JAN 2008 + 1 month = ??

Or are you only ever dealing with the first 28 days of any month ?

 

[Brad]

Not an issue as I am only adding to the 1st date of the month to begin each page with the first of the month. Then each page has its own constraints which deals with days in a month.

For example I could use
=DATE(YEAR('1'!$C$6),(MONTH('1'!$C$6)+1),DAY('1'!$C$6))

with $c$6 being a set start date

But I reckon there should be a easier way such as

=DATE + MONTH"1"

 

[john471]

Access supports this with a DateAdd function, but it is not in Excel, as far as I'm aware.

If what you've got works....

however, do you need to cater for incrementing the year too, e.g. 01-Dec-06 + 1 month --> 01-Jan-07.

 

[shades]

One way to do this. Assume your date is in B3, then in C3, put this:

=IF(OR(MONTH(B3)=4,MONTH(B3)=6,MONTH(B3)=9,MONTH(B3)=11,),B3+30,IF(MONTH(B3)=2,B3+28,B3+31))
________
Lexus is history

 

[DreamGenius]

... as I am only adding to the 1st date of the month to begin each page with the first of the month

If you're using Excel, make sure that you have the Analysis ToolPak add-in enabled (Tools > Add-Ins > Analysis ToolPak) and then use the following formula:

=EOMONTH(A1,0)+1

This has takes the cell value of A1, makes it the end of the current month (zero months ahead) and adds one day.

 

[HaHoBe]

Hi, Brad,

without the use of EOMonth, A1 holds the date, A2 the years, A3 the months to add respectively:

=DATE(YEAR(A1)+A2,MONTH(A1)+A3+(DAY(A1+1)=1),(DAY(A1+1) <>
 1)*MIN(DAY(A1),DAY(DATE(YEAR(A1)+A2,1+MONTH(A1)+A3,))))

Ciao,
Holger

 

[jaredall]

Try the EDATE function in Excel. You will need to have installed the Analysis ToolPak add-in. You can look up EDATE's use in the Excel help file.

The function will account for different numbers of days in months and for leap year. For example, EDATE(1/31/08,1) will return 2/29/08. It will return a value as a serial date, so you will have to make sure your format in the cell is for dates. Here is some info from the help screen:

Cell A1 formatted as date contains 1/15/2008

Formula Description (Result)
=EDATE(A2,1) The date, one month after the date above (February 15, 2008)
=EDATE(A2,-1) The date, one month before the date above (December 15, 2007)
=EDATE(A2,2) The date, two months after the date above (March 15, 2008)

This is my very first post to the forum, so I hope it helps. I have benefitted tremendously from reading all the answers and ideas found here.

 

[unmarkedhelicopter]

All you need is :-
(say previous date is in A1)
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
no toolpak, no nothin' and it wraps round years too.

 

[jaredall]

All you need is :-
(say previous date is in A1)
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
no toolpak, no nothin' and it wraps round years too.

This method is usually convenient, but it also is a bit quirky in dealing with months of differing lengths. For example, =DATE(YEAR(A1),MONTH(A1)+2,DAY(A1)) will return 3/2/08 for the input of 12/31/07. If I add 2 months to 12/31/07, I would hope to get 2/29/08.

Whatever solves the issue most efficiently is best, as long as your needs do not outgrow your current solution

 

[boblarson]

Access supports this with a DateAdd function, but it is not in Excel, as far as I'm aware.

DateAdd is available in Excel, but only if you use VBA.

 

[unmarkedhelicopter]

This method is usually convenient, but it also is a bit quirky in dealing with months of differing lengths. For example, =DATE(YEAR(A1),MONTH(A1)+2,DAY(A1)) will return 3/2/08 for the input of 12/31/07. If I add 2 months to 12/31/07, I would hope to get 2/29/08.

Whatever solves the issue most efficiently is best, as long as your needs do not outgrow your current solution

The above was not suggested for end of month (or anything after the 28th of any month) but mainly for 1st of month or when you just want the month/year e.g. MAR 2007, where it works infalibly.

I also notice (from re-reading the thread) that :-
a) This thread is quite old, why was it resurected ?
b) The OP actually used the same methodology in the second reply.

If you do want the end of month, again, you can do without the analysis toolpak using :- =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+1)-1