How to Create Automatic Rolling Months in Excel

While working with data sets you often find the situation to add a series of dates and that is only with the increment in the consecutive months. Many of us, may not know that Excel, has the functionality available to automate the same which can save time.

In this tutorial, we will show you some quick and easy ways to automate the rolling months within the date series.

Steps to Create Automatic Rolling Months Using Fill Handle

Fill Handle can be used to automate different types of data series, and rolling months is one of them. It is the easiest way to automate the rolling months.

1. First, enter the first date in the cell from where you want to start the data series.
2. After that, hover the cursor on the “Fill Handle” at the right bottom of the cell.
3. Now press and hold the mouse over there and then scroll downwards and it will create the date series but with the increment in the dates.
4. Once done, click on the “Fill handle” and then choose the “Fill Months” option.
5. The moment you choose “Fill Months” it will make update the series with the increments only in the rolling months.

Automate Rolling Months Using Fill Option

1. First, enter the date in the cell and then hover the cursor on the “Fill Handle“at the right bottom of the cell.
   
2. After that, press and hold the mouse over there and then scroll downwards and it will create the date series but with the increment in the dates.
   
3. Now, select the entire series and go to the “Home” tab then click on the “Fill” icon under the “Editing” group and choose the “Series” option.
   
4. In the “Series” dialog box, choose the “columns” then “Date” then “Month” option and then enter 1 as a value within the “Step value” field and click OK.
   
5. The moment you click OK, your selected date series will get updated with the increments only in months.
   

Automate Rolling Months Using Formula

You can also create the date series with the rolling months using the combination of DATE, YEAR, MONTH, MOD, and DAY functions.

=DATE(IF(MONTH(A2)+1>12,YEAR(A2)+1,YEAR(A2)),IF(MONTH(A2)+1>12,MOD(MONTH(A2)+1,12),MONTH(A2)+1),DAY(A2))

1. First, enter the first date in the cell from where you want to start the data series.
2. After that, in the next cell just below that cell write the combined formula given above and you will get the data with the rolling month.
   
3. Now, using the “Fill Handle” option, create the data series downwards.
   
4. At this point, your date series has been created with the rolling months.
   

How this Formula Works

To understand the formula. Let’s divide the formula into three parts:

=IF(MONTH(A2)+1>12,YEAR(A2)+1,YEAR(A2))

In the first part, if the month plus 1 total is greater than 12, it will return the year value by adding 1 into it otherwise will return the same year that we have in the date.

=IF(MONTH(A2)+1>12,MOD(MONTH(A2)+1,12),MONTH(A2)+1)

In the second part, if the month plus 1 total is greater than 12, it will divide the month plus 1 total with the 12 and return the remainder as a result else will add 1 into the month as a result

In the third part, the “Date” function will take the resulting month and year values from the first and second parts of the formula and will add the day number of the date into it.