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

HansVogelaar

MVP

Mar 18, 2022

kelvinmc

You could do the following:

Let's say the dates are in A2 and down.

Enter the starting date in the first cell (A2), e.g. 27/02/2022.

In the cell below (A3), enter the formula =A2+1

Fill or copy down from A3 as far as needed.

Next time, you will only have to change the date in A2, and all dates in the cells below will automatically follow suit.