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.


Download the Practice Workbook


5 Suitable Ways to Add Months to Date in Excel

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.

How to Add Months to Date in Excel


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.

EDATE(start_date, months)

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.
=EDATE(C5, D5)

Using EDATE Function to Add Months to Date in Excel

Formula Explanation:

Here in this formula, all our Order Dates are in the C column and Processing times are in the D column. That’s why I have passed C5 cell as my starting date and D5 as my month’s number.

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

Using Fill Handle Tool to Add Months to Date in Excel

Then you will get the output like the image below.


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.

MONTH(serial_number)

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.

DATE (year, month, day)

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 for showing this method we will consider the same example above.

Steps:

  • Enter the formula in Cell E5.
=DATE(YEAR(C5),MONTH(C5)+D5,DAY(C5))

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

Formula Explanation:

As I have mentioned earlier DATE function takes three arguments in its parameter. For this example we only need to increase the month, that’s why in the second argument I have passed MONTH(C5)+D5  to increase the month by a specific number. Others will be the same as it was.

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

Using Fill Handle Tool to Add Month to Date in Excel

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

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


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.

Using COUNTIFS and EDATE Functions to Add Months to Date

Steps:

  • Enter the formula in Cell F6 and press Enter.
=COUNTIFS(C5:C12,">="&F4,C5:C12,"<"&EDATE(F4,4))

Formula with COUNTIFS and EDATE Functions to Add Months to Date

Formula Explanation:

Here additionally I have used the COUNTIFS function with EDATE. Let’s see the COUNTIFS function first.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

This is almost the same as the COUNTIF function but the only basic difference is that it can take multiple ranges and conditions at a time in its parameter as an argument. In summary, it can be an alternative to multiple COUNTIFS functions.

Using the COUNTIFS function we are counting the cells with this range C5:C12  and our condition is from the date in cell F4 to the next 4 months which is calculated by the EDATE function.


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.

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

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 Fill Series Option to Add 1 Month to Date in Excel
  • Select Columns in Series.
  • Then select Date as Type.
  • Date Unit will be Month.
  • Step value will be 1.
  • Then press the OK button.
    Choose Proper Options to Add 1 Month to Date in Excel

Soon after, all the dates will be entered automatically.


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.

Using Excel VBA to Add Months to Date

Steps:

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

Insert Module in VBA to Add Months to Date

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

Insert Codes in the Module to Add Months to Date

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


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.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo