Forum Discussion
kelvinmc
Mar 18, 2022Copper Contributor
Excel add 1 month to a column of dates
Hi I have a spreadsheet that I use every month and copy over the sheet for the next month but have to go down the date column to manually change the date by one month. Is there a formula that I can u...
- Mar 19, 2022
You could run a macro:
Sub AddMonth() Dim r As Long Dim m As Long Application.ScreenUpdating = False m = Range("B" & Rows.Count).End(xlUp).Row For r = 2 To m Range("B" & r).Value = DateAdd("m", 1, Range("B" & r).Value) Next r Application.ScreenUpdating = True End Sub
kelvinmc
Mar 19, 2022Copper Contributor
Hans , Peter's first suggestion is to complex for me and because the dates are not consecutive (I have tried) your suggestion but it did not work for me.
HansVogelaar
Mar 19, 2022MVP
You could run a macro:
Sub AddMonth()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
m = Range("B" & Rows.Count).End(xlUp).Row
For r = 2 To m
Range("B" & r).Value = DateAdd("m", 1, Range("B" & r).Value)
Next r
Application.ScreenUpdating = True
End Sub