Forum Discussion
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...
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
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..
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.