Formula or macro for changing month date and year

[kenette]

Hello,

I'm a newbie to formulas or macro. I have this long list of dates in a column. I manually change the month except for the year and day. I do this monthly for reporting purposes to accounting for due dates of clients. It is tedious and time consuming for me. I wanted to automatically change the month in the column. The month is in numerical value (Accounting wanted it that way). I tried CTL H but it messes the other values when it finds similar values. Below is a sample of my list and what I would like to change within the column only. I will worry later if I have to change by the end of the year from 2016 to 2017.

[TABLE="width: 196"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]From[/TD]
[TD]To[/TD]
[/TR]
[TR]
[TD]01/19/2016[/TD]
[TD]02/19/2016[/TD]
[/TR]
[TR]
[TD]01/16/2016[/TD]
[TD]02/16/2016[/TD]
[/TR]
[TR]
[TD]01/22/2016[/TD]
[TD]02/22/2016[/TD]
[/TR]
[TR]
[TD]01/28/2016[/TD]
[TD]02/28/2016[/TD]
[/TR]
[TR]
[TD]01/14/2016[/TD]
[TD]02/14/2016[/TD]
[/TR]
[TR]
[TD]01/29/2016[/TD]
[TD]02/29/2016[/TD]
[/TR]
[TR]
[TD]01/26/2016[/TD]
[TD]02/26/2016[/TD]
[/TR]
[TR]
[TD]01/25/2016[/TD]
[TD]02/25/2016[/TD]
[/TR]
</tbody>[/TABLE]

I hope someone could help.

Thank you in advance.

 

[AliGW]

Try this:

=EDATE(A2,1)

 

[kenette]

Try this:

=EDATE(A2,1)

Would it be possible to make the formula on the column of the list itself? If not, this will do. I will have to copy and paste them as values.

Thank you.

Kenette

 

[MARK858]

Would it be possible to make the formula on the column of the list itself? If not, this will do. I will have to copy and paste them as values.

Thank you.

Kenette

With VBA?

Sub addmth()
Dim i As Long
Application.ScreenUpdating = False

        For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
            Range("A" & i).Value = DateAdd("m", 1, Range("A" & i).Value)
        Next i
Application.ScreenUpdating = True
End Sub

 

[kenette]

With VBA?

Sub addmth()
Dim i As Long
Application.ScreenUpdating = False

        For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
            Range("A" & i).Value = DateAdd("m", 1, Range("A" & i).Value)
        Next i
Application.ScreenUpdating = True
End Sub

I'm going on a holiday. I will try this when I get back at the office next week.
Thank you for the VBA code. Honestly I don't have any clue how to make one or how it works.

Kenette

 

[MARK858]

I'm going on a holiday. I will try this when I get back at the office next week.
Thank you for the VBA code. Honestly I don't have any clue how to make one or how it works.

Kenette

Seeing as you are going on holiday I will post some instructions on using a macro sometime over the weekend.

 

[kenette]

Seeing as you are going on holiday I will post some instructions on using a macro sometime over the weekend.

Back from long holiday. I Googled and was able to make the macro run. I had a few mistakes before I knew were to run it. Thanks a lot for this. Now I have two solutions usable on my other Excel reports.

Thank you.


Kenette

 

[MARK858]

Apologies, forgot I was going to post instructions. Happy you got there without my help (making the mistakes has probably taught you more anyway ).