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 19, 2022

kelvinmc

Have you actually tried the suggestions provided?

kelvinmc

Copper Contributor

Mar 19, 2022

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
MVP
Mar 19, 2022
kelvinmc

P.S. Why would you want to have a date in 3033? <grin>
- kelvinmc
  Copper Contributor
  Mar 19, 2022
  Thats why I want to do it automatically ☹️. I am going to try your macro, it will take some time , 20 years since I did any programming so will need to do some research.
  - HansVogelaar
    MVP
    Mar 19, 2022
    kelvinmc
    
    Press Alt+F11 to activate the Visual Basic Editor.
    
    Select Insert > Module.
    
    Copy/paste the code into the module.
    
    Switch back to Excel.
    
    Save the workbook as a macro-enabled workbook (*.xlsm).
    
    To run the macro, press Alt+F8, select AddMonth and click Run.

HansVogelaar

MVP

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