How to Create Automatic Rolling Months in Excel (3 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

When working with Excel, it is a frequent need for us to work with date-type data. Sometimes, we need to put the same date values with the only difference in consecutive months in our Excel. It is tiring and time-consuming to correct them manually. Rather, we can automate the rolling months. In this article, I will show you 3 quick ways to create automatic rolling months in Excel.

Table of Contents hide

3 Quick Ways to Create Automatic Rolling Months in Excel

1. Create Automatic Rolling Months Using Fill Handle

2. Use the Fill Option from Excel Toolbar to Create Automatic Rolling Months

3. Use Excel Formula with DAY, DATE, MONTH, YEAR, IF, and MOD Functions

How to Arrange Data According to the Automatic Rolling Months Created

Conclusion

3 Quick Ways to Create Automatic Rolling Months in Excel

Say, you are given a date of 2-May-19. Now, you need to automate the dates with rolling months as you go below the rows. You can follow any of the given ways below to do this.

1. Create Automatic Rolling Months Using Fill Handle

You can use the fill handle feature to create automatic rolling months in Excel. This is the easiest way among the ways described here. Follow the steps below to accomplish your target in this way.

📌 Steps:

First and foremost, click on cell B5 and insert the first date form in which you want dates with rolling months.

Afterward, place your cursor in the bottom right position of the cell.
Subsequently, a black fill handle will appear.
Following, drag it downward.

As a result, the cells will be filled with rolling dates. But you need rolling months.
To do this, click on the Fill Handle icon and choose the option Fill Months.

As a result, you will be able to create automatic rolling months in Excel using the fill handle. And, the final result should look like this.

2. Use the Fill Option from Excel Toolbar to Create Automatic Rolling Months

Another thing you can do to create rolling months is to use the Excel toolbar’s Fill option. Follow the steps below to accomplish this.

📌 Steps:

At the very beginning, put your first date in cell B5 and select the cells (B5:B14 here) that you want to put the rolling months.
Subsequently, go to the Home tab >> Editing group >> Fill tool >> Series…

As a result, the Series window will appear.
Following, choose the option Columns from the Series options >> choose the option Date from the Type options >> choose Month option from the Date unit options >> write 1 inside the Step value: text box >> click on the OK button.

Consequently, you will find the rolling months from your single date in Excel. For example, the result should look like this.

Similar Readings:

3. Use Excel Formula with DAY, DATE, MONTH, YEAR, IF, and MOD Functions

Moreover, you can use Excel formulas to create rolling months in Excel. Go through the steps below to achieve this.

📌 Steps:

First, put your starting date value on cell B5.
Next, click on cell B6 and insert the following formula.

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

Subsequently, hit the Enter key.

🔎 Formula Breakdown:

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

If the sum of the month of B5 cell’s date and 1 exceeds 12, it will return the year value as the year value of cell B5 plus 1. Otherwise, it would return the year value of cell B5.

Result: 2019.

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

It checks if the month of cell B5 plus 1 is greater than 12. If the value is greater than 12, the function would divide the month number of cell B5 plus 1 by 12 and would return the remainder as a result. Otherwise, it would return the month number of cell B5 plus 1 as a result.

Result: 6.

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

It would take the previous two results as year and month in the DATE function and also add the day number of cell B5 in the function.

Result: 2-Jun-2019

Afterward, place your cursor in the bottom right position of the cell and drag the fill handle below upon its appearance.

Thus, the formula will be copied to the cells below and you will find the rolling months as far as you want. Finally, the outcome should look like this.

Similar Readings:

How to Arrange Data According to the Automatic Rolling Months Created

Now, let’s go to the application side. Say, you are given a dataset with items produced numbers and sales amounts at different dates for an industry. Now, you need to find the items produced and sales values for only the second date of each month.

Follow the steps below to accomplish this.

📌 Steps:

First and foremost, click on cell F5 and insert the starting value of rolling months.

Afterward, click on cell F6 and insert the formula below to get rolling months for all the cells below.

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

Subsequently, press the Enter key.

At this time, place your cursor in the bottom right position of the cell.
Following, drag the fill handle below upon its arrival.

At this time, click on cell G5 and insert the formula below.

=INDEX($C$5:$C$23,MATCH(F5,$B$5:$B$23,0),1)

Subsequently, press the Enter key to find items produced for that particular date.

Afterward, use the fill handle feature to get all the produced items for all dates.

Now, click on cell H5 and insert the formula below.

=INDEX($D$5:$D$23,MATCH(F5,$B$5:$B$23,0),1)

Following, press the Enter key.

Finally, use the fill handle feature to get all the sales values for all required dates.

Thus, you will be able to extract all the values you want from the dataset according to your required date series. And, the final result should look like this.

Read more: How to Autofill Dates in Excel

Download Practice Workbook

You can download our practice workbook from here for free!

Automatic Rolling Months.xlsx

Conclusion

So, in this article, I have shown you 3 quick ways to create automatic rolling months in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. And, you are welcome to comment here if you have any further questions or recommendations regarding this article.

Thank you!

How to Create Automatic Rolling Months in Excel (3 Quick Ways)

3 Quick Ways to Create Automatic Rolling Months in Excel

1. Create Automatic Rolling Months Using Fill Handle

2. Use the Fill Option from Excel Toolbar to Create Automatic Rolling Months

3. Use Excel Formula with DAY, DATE, MONTH, YEAR, IF, and MOD Functions

How to Arrange Data According to the Automatic Rolling Months Created

Conclusion

Related Articles

Leave a reply Cancel reply