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

PeterBartholomew1

Silver Contributor

Mar 18, 2022

kelvinmc

Do you wish to calculate one month's dates from the previous (bearing in mind the number of days will usually change) or create a list of dates from the top row?

Using 365

= EOMONTH(month,-1)+
  SEQUENCE(EOMONTH(month,0)-EOMONTH(month,-1))

generates a set or dates, where 'month' is any date within the target month. With later versions of 365 one could have

= LET(
  baseDay,  EOMONTH(month,-1),
  finalDay, EOMONTH(month,0),
  day, SEQUENCE(finalDay-baseDay),
  baseDay + day)