In some cases, we have to find the date after incrementing 1 month. Do you know the easiest ways and functions to do that? In this article, I’ll focus on how to increment a month by 1 in Excel with 8 handy ways. All these methods include some wonderful Excel commands and formulas.
Download Practice Workbook
Get this sample file to try the methods by yourself.
8 Handy Ways to Increment Month by 1 in Excel
For illustration, we have a dataset of actual dates in the cell range B5:B16 before incrementing. Then, we will calculate the 1-month increment of the given dates.
Now, follow the methods below for incriminating these dates.
1. Use AutoFill Option to Increment by 1 Month
AutoFill is a feature that enables the user to expand a sequence of numbers, dates, or even text to the required cell range. You can increment 1 month easily using this feature. For this, follow the steps below.
- First, choose cell B5, and type the first date from the dataset.
- Then, select cell B5 and move the cursor to the lower right corner of the cell.
- Following, drag down the cursor using the AutoFill tool.
- Now, you’ll see the AutoFill options.
- From the options, select Fill Months.
- Finally, the results will be like the following.
Read More: How to Autofill Dates in Excel
2. Apply Excel Fill Series Command for Incrementing
Apart from the first method, you may fill the same date for consecutive months with the Fill Series option. For this, you may proceed with the following steps.
- In the beginning, choose blank cell B5 and type a date.
- Then, select a cell range according to your requirement including the cell B5.
- Now, go to the Home tab and click on Fill from the Editing group.
- Afterward, select Series from the drop-down section.
- Next, select Columns, Date and Month as the Series in, Type and Date unit options respectively.
- Also, declare the Step value as 1.
- Lastly, press OK.
- Finally, you’ll get the output like the following figure.
Read More: Use Autofill Formula in Excel (6 Ways)
3. Insert DATE Function to Increment by Month in Excel
You may use the DATE function for incrementing 1 month easily. Along with it, we will use the YEAR, MONTH, and DAY functions to extract the year, month, and date respectively. Now, follow the steps below.
- First, insert this formula in cell C5 beside the original date.
=DATE(YEAR(B5),MONTH(B5)+1,DAY(B5))
- Then, press Enter and you will see the first output.
- Following, apply the same formula in the cell range C6:C16 to get this final result.
Read More: How to AutoFill Months in Excel (5 Effective Ways)
Similar Readings
- How to Autocomplete Cells or Columns From List in Excel
- Autofill a Column in Excel (7 Easy Ways)
- How to Use Flash Fill in Excel to Split Data (4 Quick Ways)
- Filling a Certain Number of Rows in Excel Automatically (6 Methods)
- AutoFill Formula to Last Row with Excel VBA (5 Examples)
4. Increment Month by 1 with EDATE Function
The EDATE function is an excel date and time function that adds a specified number of months to a date and returns it in a numerical format. It is one of the easiest functions to get the date after incrementing months.
- First, select cell C5 and insert this formula.
=EDATE(B5,1)
- Then, press Enter.
- Lastly, apply the AutoFill tool and you will get all the incremented dates.
Read More: Enter Sequential Dates Across Multiple Sheets in Excel
5. Utilize IF Function for Incrementing in Excel
The IF function is a frequently used function in Excel, as it enables logical comparisons between a result and an expected value. Let’s use this function to increment the month by 1.
- First, type this formula in cell C5.
=IF(DAY(EDATE(B5,1))<DAY(B5),NA(),EDATE(B5,1))
- Then, hit Enter.
- Lastly, apply a similar formula for each date and you will get this final output.
Read More: How to Autofill Dates in Excel Without Dragging (7 Simple Methods)
6. Increment Month by 1 with EOMONTH Function
Basically, the EOMONTH function is not for finding the increment month by 1 in Excel. Rather, it has been used for finding the last days of the month from your given date. Now, any question revolving in your mind why we are discussing this function here? In certain cases, we may increment 30 days (1 month) from the first date till the last date of the month. So, we can use the function in those special cases.
- First, type this formula in cell C5.
=EOMONTH(B5,0)
- Then, press Enter to get the first output.
- Finally, use the AutoFill tool to get all the last days in the cell range C6:C16.
7. Combine DATE & SEQUENCE Functions to Increment Month
You can combine the DATE and SEQUENCE functions for incrementing months by 1. You can also get only the incremented month names with this function. To do the task, follow the steps below.
- In the beginning, select cell B5 to type this formula.
=DATE(2021,SEQUENCE(12),1)
- Following hit Enter and you will get the series of incremental dates in the cell range B5:B16.
- Now, apply the same formula in the cell range C5:C16.
- Then, right-click on it and select Format Cells from the Context Menu.
- Next, go to the Custom section from the Number tab.
- Here, insert the Type as mmmm and press OK.
- Finally, you will get incremented months like this.
Read More: How to Autofill Days of Week Based on Date in Excel (5 Easy Ways)
8. Combine EDATE & SEQUENCE Functions for Incrementing
To create a month sequence by 1 along with a date and to start that with a specific date, use the SEQUENCE function along with the EDATE function.to do the task, follow the process below.
- First, insert your required Start Date in cell C4.
- Then, insert this formula in cell B7.
=EDATE(C4, SEQUENCE(12, 1, 0))
- Lastly, hit Enter.
- That’s it, you will get the sequential dates all at once.
Read More: How to Create Automatic Rolling Months in Excel
Things to Remember
- Make sure all the dates are in the same format after incrementing them by 1. Otherwise, if you get any numeric value after applying any of the above methods, simply change the format from the Format Cells section.
Conclusion
In the end, we hope that it was a helpful article for you on how to increment month 1 in excel in 8 handy ways. Choose anyone according to your requirements. However, if you have any suggestions, leave them below in the comments section. Thanks for visiting ExcelDemy, a valuable source of Excel problem solutions.
Related Articles
- How to Number Columns in Excel Automatically (5 Easy Ways)
- Auto Populate Cells in Excel Based on Another Cell
- How to Apply AutoFill Shortcut in Excel (7 Methods)
- Create a Custom AutoFill List in Excel (2 Quick Methods)
- How to AutoFill Formula When Inserting Rows in Excel (4 Methods)
- AutoFill Sequential Letters in Excel (5 Quick Ways)
- How to Auto Number Cells in Excel (10 Methods)