Hassan Shuvo
Suppose, you want to list a particular date of every month when you give out the salary of your employees or maybe a list where you calculate your expenses for a month at a particular date of every month. So you need a column in your Excel worksheet where you want to increment your month by 1. Today in this article we’ll show you how you can achieve this with some simple Excel tricks.
📁 Download Excel File
You can download this to practice and understand clearly.
Learn to Increment Month by 1 with These 7 Methods in Excel
To increment month by 1 we will be using 7 different approaches. Some of them will be Excel’s own built-in feature and some of them will be using Formula. Apart from using Features from Ribbon, we will use functions like DATE, EDATE, IF, and SEQUENCE.So, Let’s get to the main part of the article.
1. Using AutoFill Feature
The first method is always the simplest one. To increment a month by 1, Excel has some built-in cool features. The Autofill Feature is one of them. To understand properly we have Formatted our cell types to show the month name. Fear not, we will be showing how you can do both in the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the column where you want to put your dates. Simply bring your cursor to the column header (the cursor will into a downward arrow) and click on it.
- Next, go to the Home Tab where you need to find the Cells group. Here, click on Format.
- Consequently, at the end of the Format drop-down menu, you will see Format Cells. Click on it.
- As a result, the Format Cells dialogue box will appear. Now, go to the Category section and find the Date, clicking on it will bring you a list of date types. Select your preferred one. We will be selecting the dd-mmm-yyyy date type. You can also use Ctrl+1 to summon the Format Cells dialogue box.
- Now that we have selected our Date type we will be incrementing the month by 1. So, put your first date in your desired cell as we did here in cell B6. You can just write the date in the following format in the Formula Bar. The date will automatically turn into the format we selected.
- After selecting your cell, bring your cursor to the right corner of the border. By this point, you’ll notice your cursor turning into a plus (+) sign. This is called the Fill Handle or AutoFill tool.
- Afterward, clicking on it just drag it down and you will see your dates are incremented by 1 day.
- Now, at the right corner of your cell range, the AutoFill option will appear.
- Finally, click on it and select Fill Months.
As a result, you will see your desire to increment month by 1 will be fulfilled.
1.1. Setting Increment
You can also achieve the same result without accessing AutoFill options. Nevertheless, you have to let Excel know that you want your month to increment by 1. I will show you how with these steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, you fill in the first date in a cell as I did in cell B6.
- Second, go to the next cell below and type in the next date you want to see. I typed here in B7.
- Then, place your cursor on the bottom right corner of the selection border until Fill Handle appears.
- Finally, holding the Fill Handle, drag it down.
Thus, you’ll be presented with a list of dates that have been equally incremented.
2. Utilizing Fill Series
There is another built-in feature from Excel you can use which is the Fill option in the Home tab. Let’s show you step by step.
⬇️⬇️ STEPS ⬇️⬇️
- First, type in the first date in your destination cell as we did in B6.
- Next, select your range of cells. It may be the whole column if you want for the whole or it may be a little like we have in this example, B6:B17.
- Now, navigate to the Home tab and select Fill which could be found in the Editing Then, from the drop-down menu, pick Series.
- Following this, the Series dialogue box will show up. Here you have some work to do. Like, for putting in a Column in the Series in section, Type– Date as we are working with dates, and Date unit– Month. You also have to put the value of the steps you want to increment. We will give 1 here. You can keep the Stop value box empty if you want. Click Ok to apply changes.
Hence, you will see your dates have been incremented as you commanded.
3. Implementing DATE Function
If you want to apply functions to increment month by 1, you can do that too. In this section, we will show, how you can use the DATE function to increment month by 1 in Excel. Let’s see the syntax of this Function.
DATE(year,month,day)
As you can see the function requires the input of year, month, and day. Certainly, we won’t be putting these values individually. When you need to merge three independent data to make a date, you can use Excel’s DATE function. We already have a list of dates and we want the DATE function to extract the required inputs from that. That’s why we will use the YEAR, MONTH, and DAY functions in conjunction with the DATE function. Let’s show you the steps in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- As usual, first, select your output cell. We will be selecting C6.
- Next in the Formula Bar, write the following formula.
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
- Again, pressing Enter will show our result. The date has been forwarded to the next month.
- Furthermore, use the Fill Handle tool to copy the formula to the rest of the column. You will see your results have incremented by 1 month.
🔨 Formula Breakdown
👉 In the formula, YEAR, MONTH, and DAY functions extracted the respective data from B6 where the date was in the dd-mmm-yyyy format.
👉 Again, in the second section of the DATE function we added 1 to our month’s data. Now when the DATE function prints our dates, it will only increment by 1 to month’s value.
📕 Read More: 6 Ways to Fill Down to Last Row with Data Not Blank in Excel
4. Employing EDATE Function
The EDATE function in Excel can be employed to increase months in dates. This function accepts two arguments: a date and an integer, and it produces a new date that is the given number of months before or after the actual date. We will use the same example as before to explain this function.
Syntax of the function:
EDATE(start_date, months)
The arguments are:
start_date – A date denoting the start date.
months – The number of months preceding or following the start date. A positive number for months results in a future date, whereas a negative value results in a past date.
Both arguments are required in this function. Now, Let’s jump to the Excel sheet for a detailed explanation.
⬇️⬇️ STEPS ⬇️⬇️
- As always, First, select your destination cell.
- Next, write this formula in the Formula Bar.
- Now, press Enter to see the results.
- To conclude, use the Fill Handle to copy the formula to the entire column.
📕 Read More: 4 Ways to Repeat Rows a Specified Number of Times in Excel
5. Assigning IF with DAY and EDATE Functions
The IF function is one of the most popular and used functions among Excel users. Although it is a conditional function you can increment a month by 1 using it. However, we’ll have to nest some other functions like DAY and EDATE into the IF function to work properly. In the example, we already have a list of dates and we want to increment them by 1 month. Before going to Excel we must take a quick look at the IF function.
Syntax:
IF(logical_test, value_if_true, [value_if_false])
Here,
Logical_test – The condition you want the function to check.
value_if_true – The value you want to be returned if the logical test returns TRUE.
value_if_false – The value you want to be returned if the logical test returns FALSE.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your output cell. We selected cell C6.
- Next, type in the following formula.
=IF(DAY(EDATE(B6,1))<DAY(B6),NA(),EDATE(B6,1))
- Now, hit Enter to see the result.
- Finally, copy the formula in the entire column using Fill Handle.
🔨 Formula Breakdown
👉 In the first segment of the IF function where condition must be given, we provided DAY(EDATE(B5,1))<DAY(B5) to check whether our month is less than the initial date.
👉 Again, in the value_if_true we used NA() to show #N/A code if the condition turns TRUE.
👉 Finally, to print our result we typed EDATE(B6,1) in the valie_if_false segment which is our incremented month.
6. Applying DATE and SEQUENCE Functions Together
We can also use the DATE and SEQUENCE functions to achieve the same results. Besides, the beauty of this method is that we can showcase the entire month in one go. Let’s check it out. But as usual, we will explain the new Function first.
Syntax of SEQUENCE function:
SEQUENCE(rows,[columns],[start],[step])
The arguments:
rows – The number of rows that will be returned. This argument is a must.
columns – Like before, the number of columns is to be returned, but it is optional.
start – The first digit of the sequence. It is also not mandatory.
step – The amount by which each consecutive value in the array is increased. It is also optional.
Any optional arguments that are missing will be set to 1. However, we’ll only use the SEQUENCE function to tell Excel how many Rows it needs to increment the months. Let’s work in Excel for proper understanding.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your desired cells where you want your dates to display.
- Secondly, write this formula in the Formula Bar.
=DATE(2022,SEQUENCE(12),2)
- Finally, hit Enter to see your entire year printed in the column.
🔨 FORMULA BREAKDOWN
👉 When you wrote SEQUENCE(12) in the month’s section of the DATE function we commanded Excel to print every month sequentially up to 12 Rows.
👉 Then, the DATE function printed the dates taken year as 2022 and day as 2.
📕 Read More: 6 Ways to Autofill Sequential Letters in Excel
7. Merging EDATE and SEQUENCE Functions
We can also use EDATE and SEQUENCE functions in a similar fashion to increment month by 1 in Excel. The steps are given below.
⬇️⬇️ STEPS ⬇️⬇️
- We first need to put our initial date as we did here in cell B6.
- Next, in cell C6, we will the following formula.
=EDATE($B$6,SEQUENCE(12))
- Finally, pressing Enter will get you the incremented months in one go.
🔨 Formula Breakdown
👉 As previously explained, by SEQUENCE(12) we demanded 12 incremented months to show in the column.
👉 Finally, the EDATE function’s first argument required the first date so we put the location B6 here. We also made the cell absolute so that it doesn’t change.
📕 Read More: 4 Ways to Add Excel Sequence Number by Group
📄 Important Notes
🖊️ The result of the EDATE function is displayed as a serial number in general format; Therefore, you must format the result as a date.
🖊️ If you don’t change the formatting of the column, the dates will show as a number.
📝 Takeaways from This Article
📌 Users will get acquainted with Excel’s inbuilt feature by reading this article.
📌 As we explained different functions in plain language, readers will be able to use them in different scenarios.
📌 Readers will be able to nest different functions with ease.
Conclusion
So that’s it, folks. I hope I explained these procedures simply enough for you to grasp them. I also believe reading this article will give you confidence in the future when you increment month by 1 in Excel. Stay with us for more cool new Excel tips and tricks and visit Excelden.com to avail of them. Thanks.
Related Articles
- 3 Ways to Auto Number or Renumber After Filter in Excel
- 5 Ways to Fill Down to Last Row with Data in Excel
