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
Similar Readings:
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill from List in Excel
- How to Create a Custom AutoFill List in Excel
- How to Fill Down Blanks in Excel
- How to Apply AutoFill Shortcut in Excel
- How to AutoFill Sequential Letters in Excel
- How to Auto Generate Number Sequence in Excel
- How to Add Sequence Number by Group in Excel
- How to Repeat Number Pattern in Excel
- Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
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 AutoFill Numbers in Excel
- How to Autofill Numbers in Excel Without Dragging
- How to AutoFill Ascending Numbers in Excel
- How to Auto Number or Renumber after Filter in Excel
- How to Auto Number Cells in Excel
- How to AutoFill Numbers in Excel with Filter
- How to Enter Sequential Dates Across Multiple Sheets in Excel
- How to Increment Month by 1 in Excel
- How to Autofill a Column in Excel
- How to Fill Column in Excel with Same Value
- How to Fill Down to Last Row with Data 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.
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!
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
- How to Autofill Dates in Excel Without Dragging
- How to Autofill Days of Week Based on Date in Excel
- How to AutoFill Months in Excel
- How to Turn Off AutoFill in Excel
- How to AutoFill Formula When Inserting Rows in Excel
- Filling a Certain Number of Rows in Excel Automatically
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Solved:] Excel Double Click AutoFill Not Working
- Excel VBA: Autofill Method of Range Class Failed
- How to Use VBA AutoFill in Excel
- How to Auto Populate from Another Worksheet in Excel
- How to Perform Predictive AutoFill in Excel
- Applications of Excel Fill Series
- [Fix] Excel Fill Series Not Working
- Fix: Excel Autofill Not Working
- [Fixed!] AutoFill Formula Is Not Working in Excel Table
- AutoFill Formula to Last Row with Excel VBA
- Drag Number Increase Not Working in Excel