# 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.

## 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.

Read More: How to Use Autofill Formula in Excel

### 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.

Read more: How to Repeat Formula Pattern in Excel

### 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.

## 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Â

## 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!

## Related Articles

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelorâ€™s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF