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

kelvinmc

Copper Contributor

Mar 19, 2022

Not sure if I have been clear in my needs. I am using Excel 365 for mac. UK

Every month I copy last months worksheet to a new work sheet and I want to update the month right down the column. Currently I do it manually. I can change the date using the formula

=EDATE(b2,1) but I want that to return the date into b2, not the current cell in which the formula is.

example below

A1 B2. B2

28/02/2022	----->	28/03/3033
28/02/2022	----->	28/03/2022
01/03/2022	----->	01/04/2022
01/03/2022	----->	01/04/2022
01/03/2022	----->	01/04/2022
01/03/2022	----->	01/04/2022
01/03/2022	----->	01/04/2022
01/03/2022	----->	01/04/2022
01/03/2022	----->	01/04/2022
03/03/2022	----->	03/04/2022
04/03/2022	----->	04/04/2022
04/03/2022	----->	04/04/2022

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