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

final result

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.

final result

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 icon1 to open different formats. Select “More Number Formats” to continue.

Home→ Number Ribbon→ Number Format→ More Number Formats

creating table

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

selecting date format

Step-2:

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

creating table

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

=EDATE(B4,C4)

Where,

  • Start_date is B4
  • Months is C4

applying formula

Press “Enter” to get the result.

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

adding months to dates

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.

adding month to dates

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.

Subtract month to a date

Step-2:

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

applying formula

Press “Enter” to get the result.

getting result

Step-3:

The values after the decimal point are also ignored here.

final result

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.

creating table

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,

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

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.

Applying the DATE function

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

getting result

Step-3:

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

final result

Step-5:

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

final result

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

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo