Excel Add Months to Date (4 Easiest Examples)

Result using EDATE function

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

Download the Practice Workbook

4 Ways to Add Months to Date in Excel


1. Add Months to Date in Excel Using EDATE

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. For more information visit this Link

For this method let’s assume we have a dataset of some orders. The attributes are Order ID, Order Date, Process Time (Months), Delivery Date. Our task will be to automatically calculate delivery dates by adding processing time with the order date.

Add months to date using EDATE function

Step 1: Enter the formula in cell E4.

=EDATE(C4, D4)

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 C4 cell as my starting date and D4 as my month’s number.

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

Enter formula using EDATE

Step 2: Copy down the formula up to cell E11.

Copy down the formula up to E11


2. Add Months to Date in Excel Using MONTH & Date Function

Now let’s see how to add months to date using the MONTH and DATE function. 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 DATE function. For more information visit this Link

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. For more information, you can visit this Link

Now for showing this method we will consider the same example above.

Step 1:  Enter the formula in cell E4.

=DATE(YEAR(C4),MONTH(C4)+D4,DAY(C4))

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(C4)+D4  to increase the month by a specific number. Others will be the same as it was.

Enter the formula using DATE and Month function

Step 2: Copy down the formula up to E11.

Copy down the formula up to E11

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


3. EDATE with Other Functions 

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.

Edate with other functions

Step 1: Enter the formula in cell F6 and press Enter.

=COUNTIFS(C5:C12,">="&F4,C5:C12,"<"&EDATE(F4,3))

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. For more details visit this Link

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

Enter the formula using Edate and press Enter


4. Increment Date by Month with Fill Series Option

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.

Increase date by month with fill series option

Step 1: Select cell C4 and select the cells up to C11.

 select the cell from C4 to C11

Step 2: Then follow the instruction

  1. Go to the Home tab.
  2. Under Editing, the section selects the Fill option.
     Open fill option

Step 3: Select the Series option.

Select Series option

Step 4: After that follow the options sequentially.

  1. Select Columns in Series.
  2. Then select Date as Type.
  3. Date Unit will be Month.
  4. Step value will be 4
  5. Then press the OK button.
    Follow the sequence

Step 5: All the dates will be entered automatically.

All the dates will entered automatically


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.

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