Unlike incrementing a whole number, incrementing the month of a date requires a little more effort in Excel because the calendar months have varying numbers of days. Microsoft gives you a couple of tools to perform this task. If you need to increment the month across a range of cells, Excel's Fill feature makes it easy. However, this might not always be feasible for discontinuous cells or increments created from dates that are subject to change. In these cases, using a formula allows you to increment the month and automatically change it if the source cell changes.
Fill Feature
-
1.
Open your spreadsheet in Microsoft Excel and locate the date you wish to increment.
-
2.
Click and drag your mouse from the cell containing the date to the last cell in the range. This will highlight all the cells where the incremented months should appear.
-
3.
Click the "Home" tab. Click "Fill" from the Editing group and "Series" from the drop-down menu.
-
4.
Click "Date" from the Type column and "Month" from the Date Unit column. Enter "1" in the Step Value field.
-
5.
Click "OK" to increment the date one month for each cell away from the original date.
Formula Method
-
1.
Open your spreadsheet in Microsoft Excel and locate the date you wish to increment by one month.
-
2.
Type "=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))" without quotes in a blank cell, replacing "A1" with the reference to the cell containing the date. In this example, the date in cell "A1" will increment by one month.
-
3.
Add dollar signs to the cell reference to keep the reference from changing if you copy the formula. In the example, "=DATE(YEAR($A$1),MONTH($A$1)+1,DAY($A$1))" will always reference cell "A1," even if you copy the formula to another cell. Otherwise, the cell reference will change to reflect your new position.
