Forum Discussion

Copper Contributor

Mar 18, 2022

Solved

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

excel

HansVogelaar

Mar 19, 2022

kelvinmc

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

harshulz

Iron Contributor

Mar 19, 2022

you don't want to change months of previous date manually? is that right?

kelvinmc

Copper Contributor

Mar 19, 2022

Ish - In a newly copied sheet I want to change the months in a column. So for example C1 has 28/02/2022 I want it to change to 28/03/2022