Forum Discussion
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
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
- you don't want to change months of previous date manually? is that right?
- 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
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 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.
- 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..
Have you actually tried the suggestions provided?
- 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.
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)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.
