How to Add Months to a Date in Excel (2 Ways)

In Excel when you have to adjust the periods of time, you can add months or years to date using some specific functions. This is quite important when you are performing financial modeling or other date-based assignments. Today in this article, we will learn how to add months to a date in Excel.

Quick View

Here is a quick view of our today’s task.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Add Months to a Date in Excel (2 Ways)

Consider a situation where you want to know the date after a specific number of months. Excel has two in-built functions that can help you in this situation. Let’s learn those methods.

1. Using The EDATE Function

The Excel EDATE function will give you the exact date if you add months to a specific date. Using this function, you can add or subtract months from the given date.

i. Adding Months to a Date

Step-1:

Let us assume that we are given some dates and some number of months. We have to find the final date after adding those months to those specific dates. To do this, create a table containing columns “Start Date”, “Months”, and “Final Date”. First, we need to set the format of the “Start Date” and “Final Date” columns. Now go to “Home”, in the “Number” ribbon, click on this icon  to open different formats. Select “More Number Formats” to continue.

Home→ Number Ribbon→ Number Format→ More Number Formats

A new window popped out. Now in the “Category”, select “Date” and in “Type”, choose the date format you like.

Step-2:

Now add those dates and months that you are given to the “Start Date” and “Months” columns.

Step-3:

In the cell “D4” of the “Final Date” column, apply the “EDATE” function. The generic EDATE function is

=EDATE(start_date, months)

Insert the values into the function and the final formula is

Where,

• Start_date is B4
• Months is C4

Press “Enter” to get the result.

Step-4:

Now move the mouse cursor to the lower right corner of the D4 cell and when the cross icon (+) is shown, double click on this to apply the same function to the rest of the cells.

Step-5:

Now here is an interesting thought! If you input the month value in fraction format the “EDATE” function will only count the integer part. That means the function will ignore the value after the decimal point. That’s why the function only considered “5” in those red marked boxes.

ii. Subtract Months from a Date

Now we will subtract months from the given dates using the same “EDATE” function.

Step-1:

In the following example, we are given this data table where the month’s values are negative.

Step-2:

Now in the D4 column, apply the same “EDATE” function that you used in the previous example.

Press “Enter” to get the result.

Step-3:

The values after the decimal point are also ignored here.

2. Using the DATE function

We can add months to date easily by using the “DATE” function. Let’s see how.

Step-1:

In the following example, we are given some dates and months in the “Start Date” column and “Months” column. We need to add these months to these dates in the “Final Date” column.

Step-2:

In the cell “D4” apply the “DATE” function. The generic “DATE” function is

=DATE(year,month,day)

Now insert values into the “DATE” function and the final formula is,

Where,

• The YEAR function looks at the date in cell B4 and returns 2017.
• The MONTH function returns the month value from cell B4. It then adds 2 months from cell
• The DAY function returns the day value from cell B4.

Now get the result by pressing “Enter”. We have got our required date.

Step-3:

Now apply the same formula for the rest of the cells.

Step-5:

The “DATE” function can also subtract months from a date. Look at the result in cell D12.

Quick Notes

👉 Change the format of the cells using the “Dates” option in the number ribbon.

👉The “EDATE” function will return #VALUE! error if the start date is an invalid date.

👉If the “Months” is not an integer, then the integer value before the decimal point is considered.

Conclusion

How to add months to a date in excel is discussed here in this article. We hope that you will find a solution to your problem in this article. If you have any thoughts or confusion regarding this article, you are welcome to comment.

Further Readings

• How to Add 7 Days to a Date in Excel (3 Methods)
• How to Add 30 Days to a Date in Excel (7 Quick Methods)
• How to Add Dates in Excel Automatically
• Excel Formula to Find Date or Days in Next Month (6 Quick Ways)
• Add Weeks to A Date in Excel [4 Fast & Simple Methods with Template]
• How to Subtract/Minus Days from Today’s Date in Excel (4 Simple Ways)
• Excel Formula to Count Days from Date
• Add Days to Date Using Excel Formula
• How to Add/Subtract Years to a Date in Excel
• Add days to date in Excel excluding weekends and holidays (4 ways)