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.


How to Create Automatic Rolling Months in Excel: 3 Quick Ways

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.

Record the First Date for Automatic Rolling Months in Excel

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

Drag Fill Handle below to Create Automatic Rolling Months in Excel

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

Choose Fill Handle Option to Create Automatic Rolling Months in Excel

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.

Created Automatic Rolling Months in Excel

Read More: How to Repeat Formula Pattern 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…

Use Fill Tool to Create Automatic Rolling Months in Excel

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

Series Window

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

Created Automatic Rolling Months in Excel

Read More: How to AutoFill Months 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 to Create Automatic Rolling Months in Excel

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

Drag Fill Handle Below

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.

Created Automatic Rolling Months in Excel

Read More: How to Autofill Days of Week Based on Date in Excel


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.

Dataset to Extract Data According to Automatic Rolling Months

Follow the steps below to accomplish this.

📌 Steps:

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

First Date Value of Date Series

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

Formula to Create Automatic Rolling Months in Excel

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

Automatic Rolling Months

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

Formula to Find Produced Items

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

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.

Formula to Find Sales Values

  • 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. The final result should look like this.

Found All Values for All Dates

Read more: How to Autofill Dates in Excel 


Download Practice Workbook

You can download our practice workbook from here for free!


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


<< Go Back to Autofill Dates | Excel Autofill | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo