Macro: Add one month to all the cells with date data in workbook [Closed]

Report
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014
-
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014
-
Hello,

Need help in a macro that will add one month to all the cells that contains date data

like 2/3/2014 will become 2/4/2014

Thank you.

5 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
try this

Sub test()
Dim c As Range
For Each c In Range("A1").CurrentRegion
If WorksheetFunction.IsNumber(c) Then c = Month(c) + 1 & "/" & Day(c) & "/" & Year(c)
Next c
End Sub
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014

Thanks for replying, but it doesn't work with my worksheet.

The dates are on column A I formatted this to ddmmyyyy but when you click the cell it will show 3/1/2014 for 01032014.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
It worked for me
it does not matter how you format it. how did you enter it-that is important
you must enter it according to regional configuration either m/d/yy or d/m/yy.
see control panel -region and language. in formula bar it will show as you have indicated. is that march 1st or 3rd january.
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014

Hi,

Its working, but the thing is, it will add numbers in all the cell that has numbers even it is not formatted as date.

Can you please help me to add one month on those in column A only.


I've tried

Sub test()
Dim c As Range
For Each c In Range("A:A").CurrentRegion
If WorksheetFunction.IsNumber(c) Then c = Month(c) + 1 & "/" & Day(c) & "/" & Year(c)
Next c
End Sub


Even changed it to IsDate


But it doesnt work.


Thank you.
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014

Hi,

Your code works when only column A has date value, but when there is a number in column B and so on, it will add something also to it.

I only want to change the dates in column A .

Thank you.

Sub test()
Dim c As Range
For Each c In Range("A1:A20")
If IsDate(c) Then c = WorksheetFunction.EDate(c, 1)
Next c
End Sub

Try this.
This uses the built-in function EDATE to achieve what you want.
It more accurate as the other method does not deal with dates that roll past 12 (December) correctly.
Change the range used accordingly
Posts
28
Registration date
Monday February 3, 2014
Status
Member
Last seen
June 23, 2014

Thank you so much. It works.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!