How to Increment Month by 1 in Excel (8 Handy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

8 Handy Ways to Increment Month by 1 in Excel

1. Use AutoFill Option to Increment by 1 Month

2. Apply Excel Fill Series Command for Incrementing

3. Insert DATE Function to Increment by Month in Excel

4. Increment Month by 1 with EDATE Function

5. Utilize IF Function for Incrementing in Excel

6. Increment Month by 1 with EOMONTH Function

7. Combine DATE & SEQUENCE Functions to Increment Month

8. Combine EDATE & SEQUENCE Functions for Incrementing

Things to Remember

Conclusion

Download Practice Workbook

Get this sample file to try the methods by yourself.

Increment Month by 1.xlsx

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.

In the formula, The DATE function returns the serial number from the date of cell B5. Then, The YEAR and MONTH functions extract the year and month from that given date. Lastly, the DAY function returns the day of the month as a number between 1 to 31 from the given date as well.

Following, apply the same formula in the cell range C6:C16 to get this final result.

Similar Readings

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.

In the EDATE formula, cell B5 is the actual date before incrementing, and 1 is for incrementing 1 month.

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.

This formula compares the converted date to the original date’s day with the IF function. We have to utilize it along with the DAY and EDATE functions for incrementing the given actual dates by 1 month. Furthermore, we have to use the NA function along with the IF function to return the #N/A error code for any blank cell.

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.

Here, cell B5 is the actual date before incrementing and 0 for the same month in the EOMONTH function.

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.

In this case, insert the target year 2021 in the 1st argument and the day as 1 in the 3rd argument. In the 2nd argument, the SEQUENCE function will return the sequential numbers from 1 to 12. Based on these parameters, the DATE function will produce a series of dates.

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.

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.

Here, The EDATE function returns a date along with a month before or after the start date in cell C4. Then, the SEQUENCE function creates an array of 12 numbers as we want to output for 12 months. Following, we typed 1 as the 2nd argument for one-month increments. Also, the start argument is set to 0 and included in the formula.

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.

How to Increment Month by 1 in Excel (8 Handy Ways)

Download Practice Workbook

8 Handy Ways to Increment Month by 1 in Excel

1. Use AutoFill Option to Increment by 1 Month

2. Apply Excel Fill Series Command for Incrementing

3. Insert DATE Function to Increment by Month in Excel

4. Increment Month by 1 with EDATE Function

5. Utilize IF Function for Incrementing in Excel

6. Increment Month by 1 with EOMONTH Function

7. Combine DATE & SEQUENCE Functions to Increment Month

8. Combine EDATE & SEQUENCE Functions for Incrementing

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply

How to Increment Month by 1 in Excel (8 Handy Ways)

Download Practice Workbook

8 Handy Ways to Increment Month by 1 in Excel

1. Use AutoFill Option to Increment by 1 Month

2. Apply Excel Fill Series Command for Incrementing

3. Insert DATE Function to Increment by Month in Excel

4. Increment Month by 1 with EDATE Function

5. Utilize IF Function for Incrementing in Excel

6. Increment Month by 1 with EOMONTH Function

7. Combine DATE & SEQUENCE Functions to Increment Month

<img decoding="async" class="aligncenter size-full wp-image-270992" src="https://www.exceldemy.com/wp-content/uploads/2021/07/Excel-Increment-Month-by-1-6.png" alt="" width="460" height="514">

8. Combine EDATE & SEQUENCE Functions for Incrementing

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply