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.


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.

Excel Increment Month by 1


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.

Use AutoFill Option to Increment by 1 Month

  • Now, you’ll see the AutoFill options.
  • From the options, select Fill Months.

Use AutoFill Option to Increment by 1 Month

  • 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.

Apply Excel Fill Series Command for Incrementing

  • Now, go to the Home tab and click on Fill from the Editing group.

  • Afterward, select Series from the drop-down section.

Apply Excel Fill Series Command for Incrementing

  • Next, select Columns, Date and Month as the Series in, Type and Date unit options respectively.
  • Also, declare the Step value as 1.

Apply Excel Fill Series Command for Incrementing

  • 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))

Insert DATE Function to Increment by Month in Excel

  • 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.

Insert DATE Function to Increment by Month in Excel

Read More: How to AutoFill Months in Excel (5 Effective Ways)


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)

Increment Month by 1 with EDATE Function

  • Then, press Enter.
  • Lastly, apply the AutoFill tool and you will get all the incremented dates.

Increment Month by 1 with EDATE Function

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

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))

Utilize IF Function for Incrementing in Excel

  • Then, hit Enter.
  • Lastly, apply a similar formula for each date and you will get this final output.

Utilize IF Function for Incrementing in Excel

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.

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)

Increment Month by 1 with EOMONTH Function

  • 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)

Combine DATE & SEQUENCE Functions to Increment Month

  • 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.

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.

Combine EDATE & SEQUENCE Functions for Incrementing

  • 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.

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

Md. Abdul Kader
Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo