How do I add one month to each date field in a column in Excel 2013
I have a column of date fields that contain different dates. I want to add 1 month to each of the dates.
So if my column/row is the following
A3: 1/2/2014 A4: 1/4/2014 A5: 1/10/2014 A6: 1/15/2014
Whatever formula or method I run will change everything to
A3: 2/2/2014 A4: 2/4/2014 A5: 2/10/2014 A6: 2/15/2014
You can use the EDATE function which adds a number of months to a give date.
will add one month to the date in cell A1.
That won't do what I need it to do though. I want to update the existing cells to increase the date in each of them. I updated my question to (hopefully) make it clearer. – Scott 33 mins ago
From the comment under the deleted answer(Jerry) (Since I can still see them :p), I guess you want to use VBA. If that is the case then see this.You need to use the DateAdd()
If you check the Excel's help, DateAdd returns a Variant (Date) containing a date to which a specified time interval has been added.
DateAdd(interval, number, date)
The interval argument has these settings:
Setting Description yyyy Year q Quarter m Month y Day of year d Day w Weekday ww Week h Hour n Minute s Second
Paste this in a module.
Sub Sample() Dim ws As Worksheet Dim lRow As Long, i As Long '~~> Change this to the relevant sheet Set ws = ThisWorkbook.Sheets("Sheet1") With ws lRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 3 To lRow .Range("A" & i).Value = DateAdd("m", 1, .Range("A" & i).Value) Next i End With End Sub
Since in your example all your dates are in January, this should work: enter 31 in some cell and copy. Select your dates and Paste Special… Add. Simple, but big disadvantage is that this only works for 7 months of the year!