Forum Discussion

kelvinmc's avatar
kelvinmc
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's avatar
    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's avatar
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
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 19, 2022

    kelvinmc 

    Is this really what you want?

    If so, the formula I used is

    = EDATE(+date,1)

    where the '+' comes about because EDATE is one of those functions that messes up on multi-cell ranges.

    • kelvinmc's avatar
      kelvinmc
      Copper Contributor
      Mar 19, 2022
      Thank you Peter, I can get the results OK from the EDATe formula, but what I want is for the results to return to the original cell in my case B2. so =EDATE(b2,1 ) works fine but I need it to "over-write" the original B2 cell. Otherwise I may as well continue to manually update the month on each new monthly worksheet. I hope I've explained myself OK..
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Mar 19, 2022

        kelvinmc 

        Since you want to overwrite previous values, you need VBA or some other imperative programming environment.  Worksheet formulas represent a functional programming style in which iterative updating is deliberately prohibited to avoid 'unintended consequences'.

         

        I still don't fully understand the use-case in which the pattern of days is repeated without regard to the length on the month or the day of the week but, then, it is your use-case.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Mar 19, 2022

    kelvinmc 

    Have you actually tried the suggestions provided?

    • kelvinmc's avatar
      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's avatar
        HansVogelaar
        MVP
        Mar 19, 2022

        kelvinmc 

        P.S. Why would you want to have a date in 3033? <grin>

Resources