The Excel EDATE function is categorized as a DATE and TIME function. It’s a handy function for when you want to calculate a date and number of months in the future or the past. Subtracting or adding months to any date can be done in Excel by using the EDATE function. This function is especially useful when determining maturity or due dates.
Sometimes, when working with dates, you may want to know the date after a specific number of months. For example, you may want to know the date four months after or before a specific date.
Fortunately , Microsoft Excel has an amazing function (EDATE) that does exactly this.
This tutorial will assist all levels of Excel users in the usage and syntax of EDATE function. You can use this function to compute the maturity of investments, accounts receivable, accounts payable and due dates for any other purpose. Let me show you how to use a simple formula to add or subtract months to a date in Excel.
Add Months to a Date
Suppose you have a dataset as shown below and you want to add the given number of months in column K to the dates in column J.
This can be done using the EDATE function and
hereby am giving syntax of the EDATE function: = EDATE(start_date, months)
start_date – A date that represents the start date in a valid Excel serial number format.
months – The number of months before or after start_date.
In our example, we want to add 5 months (cell K2) to May 31, 2021 (J2) and get the result in cell C2.
The formula in L2 looks like > = EDATE(J2, K2)
The result is March 30, 2022, as this is a date 5 months after October 30, 2021.
Note that you can use the same formula to add months as well as subtract months from any given date and also n our example, we have a couple of month values that are negative. When used in the formula, these will give you the date which is the specific number of months ago in the past.
Things to remember about EDATE in Excel:
Entering an invalid start_date will cause the function to return a #VALUE error.
The function will ignore the time component if any, is attached to the date supplied in the start_date
The argument entered in the months argument must be an integer. If you enter a decimal value, the function will ignore it.
The function’s output cell must be formatted as a date, otherwise, the date will be displayed as a serial number.
This way, we can use the EDATE function to also make the function to add/subtract years from a date. So this is a simple formula method you can use to add months or years to a date in Excel.
Thanks for reading it!