How to Add Months to Date in Excel (5 Practical Examples)

For project planning or time management, we need to make a routine or schedule. Making time schedules, MS Excel provides different ways to add months to date. In this article, I am going to show how to add months to date using Excel Functions and formulas.

How to Add Months to Date in Excel: 5 Suitable Ways

First, get introduced to our dataset that represents some orders. The attributes are Order ID, Order Date, Process Time (Months), and Delivery Date. Our task will be to automatically calculate delivery dates by adding the processing time to the order date.

1. Using EDATE Function to Add Months to Date in Excel

In this part, we will use Excel’s popular function which is the EDATE function. Before going to the main example let’s see the fundamentals of this function.

In this function, the first argument takes the starting date or the given date which will be updated. Then the number of months that will be added or subtracted from the starting date.

Steps:

• Enter the formula in Cell E5.

[Note: Make sure that your D column cells are in Date format]

• Copy down the formula by using the Fill Handle tool up to Cell E12.

Then you will get the output like the image below.

Read More: How to Add 3 Months to a Date in Excel

2. Using MONTH & Date Functions to Add Month to Date in Excel 

Now let’s add months to date using the MONTH and DATE functions. Let’s see their syntax first.

This function returns the month of a date denoted by a serial number. In its parameter, the serial number is a must. The date of the month we are attempting to find and dates should be inserted by using the MONTH function.

This Excel function is used when we need to take three separate values and merge them to form a date. We need to pass year, month, and day values to form a full date using the DATE function.

Now to show this method, we will consider the same example above.

Steps:

• Enter the formula in Cell E5.

• Later, copy down the formula by using the Fill Handle tool up to Cell E12.

Look, we got the same output as the first method.

[Note: Make sure that your D column cells are in Date format]

Read More: How to Add 6 Months to a Date in Excel

3. Combine COUNTIFS and EDATE Functions to Add Months to Date

Let’s say that we have a data set of orders. The attributes are Order ID and Delivery Date. Now we will consider the date and count how many orders will be delivered from the specific date to the next 4 months. For this example, let’s consider our time duration is June 2020 to September 2020. Here, we’ll use the COUNTIFS and EDATE functions to perform the task.

Steps:

• Enter the formula in Cell F6 and press Enter.

Read More: How Do I Add 7 Days to a Date in Excel

4. Using Fill Series Option to Add 1 Month to Date in Excel

Let’s do the same thing using the same example above but here we will increase the month by 4 for every order. So, our task will be to increase the Oder date by 4 and show them in the Delivery Date column. Our starting date is 1/2/2020. Now all the Order dates for each order will be given by increasing the current date by 4 sequentially.

Steps:

• Select Cell C4 and select the cells up to C12.
• Go to the Home tab.
• Under the Editing section, select the Fill option.
• Next, select the Series option.
  
• Select Columns in Series.
• Then select Date as Type.
• Date Unit will be Month.
• Step value will be 1.
• Then press the OK button.
  

Soon after, all the dates will be entered automatically.

Read More: How to Add 30 Days to a Date in Excel

5. Run a VBA Code to Add Months to Date

In our last method, we’ll show how to use VBA for adding months. Excel VBA has a function named DateAdd, we’ll use it here. Also, it will have input boxes, so that we can insert the date and number of months. The VBA macro will add 3 months with the date 5/2/202.

Steps:

• Firstly, press Alt + F11 to open the VBA window.
• Then, click Insert > Module to create a new module.

• Next, type the following codes in the module-

Sub AddMonths()
Dim StartDate As Date
Dim AddingMonths As String
Dim mNumber As Integer
Dim Message As String
AddingMonths = "m"
StartDate = InputBox("Insert the Starting Date")
mNumber = InputBox("Insert Number of Adding Months")
Message = "Updated Date: " & DateAdd(AddingMonths, mNumber, StartDate)
MsgBox Message
End Sub

• Then, press the Run icon to run the codes.

• After appearing the Macros dialog box, select the macro name and press Run.

• The first input box will allow you to insert the date, insert the date and press OK or Enter.

• After that, another input box will allow you to insert the number of months. Type the number and press OK or Enter.

A notification box will show the output.

Download Practice Workbook

Conclusion

These are the ways to add months to date in Excel. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.

Related Articles

• How to Add Days to a Date in Excel Excluding Weekends
• How to Add Weeks to a Date in Excel
• How to Add Years to a Date in Excel
• How to Add 2 Years to a Date in Excel
• How to Add 3 Years to a Date in Excel
• How to Create a Formula in Excel to Change Date by 1 Year

<< Go Back to Adding Days to Date | Calculate Dates | Date-Time in Excel | Learn Excel