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

Automatic Rolling Months Created by Formula in Excel

Today I will show how you can create a series of automatic rolling months in Excel and then arrange your data according to the series created.


Download Practice Workbook


Creating Automatic Rolling Months in Excel

You can create a series of Automatic Rolling Months in 3 ways.


1. Using Fill Handle

This is the easiest method. First, select a cell and enter the first date of the series you want to create. Here I select cell B4 enter 02-May-2019.

Entering a Date into a Cell in Excel

Then drag the Fill Handle upto the length of the series you want to create. Here I want to create a series of 18 cells upto cell B21.

Now from the Auto Fill Options, choose Fill Months.

Auto Fill Options in Excel

You will find a series of dates rolling by month created beautifully like this.

Automatic Rolling Months Created by Fill Handle in Excel

Read More: [Fix] Excel Fill Series Not Working (8 Causes with Solutions)


2. Using Fill Option from Excel Toolbar

You can create a series of dates of automatic rolling months by using Fill Option from Excel Toolbar.

First of all, select a cell and enter the first date of the series.

Like the previous one, I selected cell B4 and entered 2-May-19.

Entering a Date into a Cell in Excel

Then select all the cells where you want to insert the series. Here I select cells B4 to B21.

Selecting a Column of Cells in Excel

Then go to the Home>Fill option under the Editing Section of Excel Toolbar. Click the drop down menu.

Fill Option in Excel Toolbar

From the options available, select Series.

Series Option from Fill in Excel Toolbar

You will get a dialogue box called Series.

In the Series in options, check Columns.

Then in the Type options, check Date.

And then in the Date unit option, check Month.

And in the Step value box, put 1.

Series Dialogue Box in Excel

Then click OK.

You will find a series of dates rolling by month created beautifully like this.

Automatic Rolling Months Created by Fill Option from Excel

Related Content: How to Turn Off AutoFill in Excel (3 Quick Ways)


Similar Readings:


3. Using Excel Formula

You can also use Excel formulas to create a series of dates rolling by months.

First select a cell and enter the first date.

Here I select B4 and enter the date 02-May-2019.

Entering a Date into a Cell in Excel

Then put this formula in the cell B5

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

Formula for Automatic Rolling Months in Excel

Then drag the Fill Handle up to the cell in which you fill the series. I drag them up to cell B21.

And find all the cells filled beautifully with automatic rolling months.

Automatic Rolling Months Created by Formula in Excel

Note: Here B4 is the cell where I entered the first date. You can change it according to your wish and prepare the formula accordingly.

Let us explain the formula now.

  • Here MONTH(B4)+1 denotes the next month of the month of B4. For example, if B4 contains month May, MONTH(B4)+1 will denote month June.
  • But if the  month of cell B4 is December, that means MONTH(B4)=12, we can not use MONTH(B4+1)=13 to go to the next month. In that case we have to come back to January with the year increased by one.
  • The two IF functions within the formula do exactly the same thing.
  • IF(MONTH(B4)+1>12,YEAR(B4)+1,YEAR(B4) returns the same year if MONTH(B4)+1 is less than or equal to 12, and increases the year by one if it is greater than 12.
  • And IF(MONTH(B4)+1>12,MOD(MONTH(B4)+1,12),MONTH(B4)+1),DAY(B4) returns the next month if the present month is anything but December, but returns the next month as January if the present month is December.
  • Finally, the year, month and the date is confined within a DATE() function to get the complete date.

Read More: Applications of Excel Fill Series (12 Easy Examples)


How to Arrange Data According to the Automatic Rolling Months Created

Now come to the next stage. Let us have a look at this data set.

A Data Set in Excel

We have the Dates, Number of items produced and Total Sales in column B, C and D respectively.

Now if the CEO of the company wants to extract the record of the number of items produced and total sales on the 2nd instant of each month, how can he achieve that?

Very simple.  First create a series of the dates of the 2nd instants of every month in a new column.

I make it in column F and name it as Date Series.

The formula will be

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

Automatic Rolling Months Formula in Excel

Then in the adjacent column G, in the cell G4, enter this formula

=INDEX(C4:C22,MATCH(F4,B4:B22,0),1)

Extracting Data by INDEX-MATCH formula in Excel

See we have extracted out the number of items produced on 2-May-19. It was 20.

Now drag the Fill Handle through the rest of the cells to extract the number of items produced on the 2nd instants of each month.

Dragging Fill Handle to Copy Formula in Excel

Do the same for Total Sales in column H. The formula will be

=INDEX(D4:D22,MATCH(F4,B4:B22,0),1)

INDEX-MATCH Formula to Extract Data in Excel

Thus we have extracted the total sales on the 2nd instants of each month.

Related Content: How to AutoFill Months in Excel (5 Effective Ways)


Conclusion

So, using these methods, you can create a series of dates of automatic rolling months in Excel, as well as extract data from that series in Excel. Do you have any questions? Feel free to ask in the comment section.


Further Readings

Rifat Hassan

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

Leave a reply

ExcelDemy
Logo