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 use to change the dates by one month in the column of dates?

I have seen the =EDATE(start date, number of months) formula , but that does. not appear to change all the dates in the column.

By the way I use UK dates ie day month year. Also the first 2 dates in the column tend to be the last days of the month, the the new month start below.

 

Hope I have made sense

  • 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

17 Replies

  • harshulz's avatar
    harshulz
    Iron Contributor
    you don't want to change months of previous date manually? is that right?
    • kelvinmc's avatar
      kelvinmc
      Copper Contributor
      Ish - In a newly copied sheet I want to change the months in a column. So for example C1 has 28/02/2022 I want it to change to 28/03/2022
  • kelvinmc's avatar
    kelvinmc
    Copper Contributor

    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

      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
        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..
      • kelvinmc's avatar
        kelvinmc
        Copper Contributor
        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.
  • 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)

     

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

Resources