Excel Increment Month by 1 [6 Handy Ways]

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 the handy ways and popular functions to find the date after incrementing.

Download Practice Workbook

 

Excel Increment Month by 1

We have a dataset of actual dates before incrementing. Then, we have to calculate the 1-month increment of the given dates.

Dataset for incrementing 1-month

Let’s start.

1. Increment 1-Month Using AutoFill Options

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.

Steps:

  • Choose a blank cell, like B5, and type a date
  • Select the cell B5 and move the cursor to the lower right corner of the cell
  • Drag down the cursor using Fill Handle Tool
  • You’ll see the AutoFill Options. From the options, select Fill Months.
  • The results will be like the following.

Using AutoFill Options

2. Increment 1-Month by Filling with Series

Besides, you may fill the same date of consecutive months with Series option from the Home tab. For this, you may proceed with the following steps.

Steps:

  • Choose a blank cell e.g. B5 and type a date
  • Now select cell range according to your requirement including the cell B5
  • Pick the Series option by clicking Home>Fill>Series.

Filling with Series

  • Select Columns the Series in option
  • Choose Date as Type
  • Pick Month as Date Unit
  • Declare the Step value as 1
  • Now, press OK

Filling with Series

You’ll get the output like the following figure.

Output of Filling with Series

3. Increment 1-Month Using DATE Function

You may use the DATE function for incrementing 1 month easily. Only, you have to add 1 in the MONTH part of the DATE function.

The DATE function returns the serial number that a particular date holds.

The syntax of the function is

=DATE (year, month, day) 

The arguments of the function are the following

year – Number for the year.

month – Number for the month.

day – Number for the day.

As we have one cell that contains the date, we can use the YEAR, MONTH, DAY function to extract the year, month, and date respectively. Also, you can input the data manually.

The YEAR function extracts the year from a given date. The syntax of the function is

=YEAR (date) 

The Excel MONTH function extracts the month from a given date. The syntax of the function is

=MONTH (serial_number) 

The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date.

=DAY (date) 

Therefore, our formula takes the form of incrementing 1 month. Now select a cell and insert the formula

=DATE(YEAR(B5),MONTH(B5)+1,DAY(B5))

Here, B5 is the actual date before incrementing.

Press Enter and you’ll get the following output.

Using DATE Function

4. Increment 1-Month Using EDATE Function

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

The syntax of the function is

=EDATE (start_date, months)

The arguments are-

start_date – Start date as a valid Excel date.

months – Number of months before or after start_date.

Now, select a blank cell, insert the following formula and press Enter.

=EDATE(B5,1)

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

Using EDATE Function

5. Increment 1-Month Utilizing IF Function

The IF function is a frequently used function in Excel, as it enables logical comparisons between a result and an expected value.

The syntax of the function is

=IF (logical_test, [value_if_true], [value_if_false])

The arguments of the function are-

logical_test – A value or logical expression that can be evaluated as TRUE or FALSE.

value_if_true – [optional] The value to return when logical_test evaluates to TRUE.

value_if_false – [optional] The value to return when logical_test evaluates to FALSE.

Furthermore, we have to use the NA function along with the IF function.

Sometimes there is insufficient data to run the calculation. In that situation, the function checks to see if the cell in column B is blank. If this is the case, it returns the #N/A error code using NA. If a value is present, then the normal calculation is performed.

The syntax of the function is

=NA ()

We can utilize the two functions along with DAY and EDATE functions for incrementing the given actual dates by 1 month.

For this, select a blank cell, input the following formula, and finally press Enter.

=IF(DAY(EDATE(B5,1))<DAY(B5),NA(),EDATE(B5,1))

Here, B5 is the actual date before incrementing.

This formula compares the converted date to the original date’s day, and if they differ, it produces a #N/A error.

Utilizing IF Function

6. Increment 1-Month Using EOMONTH Function

Basically, the EOMONTH function is not for finding the increment. Rather, it has been used for finding the last days of the month from your given date. For example, if you input 21-Jan-2021 as the first argument in the function, 0 as the second argument, and then, you’ll get the output as 31-Jan-2021.

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.

If you have any confusion, let’s have a look at the syntax of the function.

=EOMONTH (start_date, months)

The arguments are-

start_date – A date in proper Excel serial number format.

months – Months before or after start date

Right now, select a blank cell, insert the following formula and press Enter.

=EOMONTH(B5,0)

Here, B5 is the actual date before incrementing and 0 for the same month.

Increment Using EOMONTH Function

Things to Keep in Mind

While doing the calculation, you may wonder if the output is shown as a number, not a date.

It is because the Excel deals date with a unique serial number.

So, if you see the number, just use the Format Cells and change the number format into the date.

For this, select the numbers and press CTRL+1 for Format Cells, choose the suitable format from the date, and finally press OK.

Format Cells

Conclusion

This is how we can calculate the 1-month increment using different ways and functions. Choose one according to your requirement. However, if you have any opinions, leave them below in the comments section.

Thanks for visiting exceldemy.com, a valuable source of Excel problems-solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo