Rifat Hassan
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.
Download Practice Workbook
You can download our practice workbook from here for free!
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.
- For doing 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: [Fix] Excel Fill Series Not Working (8 Causes with Solutions)
2. Use 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.
Related Content: How to Turn Off AutoFill in Excel (3 Quick Ways)
Similar Readings
- How to Number Columns in Excel Automatically (5 Easy Ways)
- Perform Predictive Autofill in Excel (5 Methods)
- How to Auto Populate from Another Worksheet in Excel
- Autocomplete Cells or Columns From List in Excel
- Automatic Numbering in Excel (9 Approaches)
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 result. Otherwise, it would return the month number of cell B5 plus 1 as 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.
Read More: Applications of Excel Fill Series (12 Easy Examples)
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.
Related Content: How to AutoFill Months in Excel (5 Effective Ways)
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.
Visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!
