This article explains the syntax and the use of the EDATE function in Microsoft Excel.
The Excel EDATE function returns the serial number that represents the date that is a specified number of months before or after a specified date. In other words, the function returns the same date in future or past months. The negative value returns the date specified number of months before a start date and positive value returns the future’s date.
You can use the EDATE function to calculate maturity dates, expiry dates, due dates, installment dates, or other such dates that fall on the same day of the month. It can also be used to subtract months to a given date.
For instance, the following function would return the serial number ‘43248’ representing 28-May-18, which is two months after the date 28-Mar-18.
=EDATE (“28-Mar-18”, 2)
EDATE (start_date, months)
- Start_date: [Required] A valid date representing initial or start date.
- months: [Required] The number of months to jump from the start_date. A positive value for months yields a future date; a negative value yields a past date.
Examples: – EDATE Function
Let’s take a few examples to understand the basic use of EDATE function.
Get the same date next month
Suppose you want to return the same date of next month from a given date or current date. You can use EDATE function to return the same easily.
In the following example, the function in cell B2 refers to the start date given in cell A2 and then returns the serial number of the same date in next month in B2.
You can further apply the custom date format on to the serial number to display it as valid excel date.
Tip: You can also use the shortcut CTRL + SHIFT + 3 to apply the default date format, i.e. ‘d-mmm-yy’ to the cell having a serial number.
EDATE with other date function
In the previous example, the current date is given in one cell, however, you can also use TODAY function directly to EDATE function to supply the start date and get the same date in next month.
Tip: You can also specify the start date and the number of months directly in the function, however, according to Microsoft, dates should be entered by using the DATE function, or as a result of other Excel functions or formulas.
Returns the date N months before or after the start date
Say you want to return the same day of the month in future or past based on the months and dates given in columns.
The following screenshot shows the result of the EDATE function in column C along with ‘Notes’ in column D.
Based on the above-given examples:-
- On row 2, the function returns a serial number of a date that is 3 months after the supplied start date.
- On row 3, the function returns a date after it subtracts one month from the start date.
- There is no 30th day in February, thus calculating 6 months after 30-Aug-17, the function returns the 28th day of February on row 4 as the last date of Feb.
- In 5th row, EDATE returns the serial number of a date that is two months in past from the start date.
- On Row 6, the function returns the same date as the start date because months is given as zero.
- And in row 7, it returns the date after two months from the start date.
Tip: EDATE returns a serial number that represents the date. You should use custom date format to a cell(s) to display them as valid excel dates.
- The function occurs #NUM! error if the result of adding the required number of months is not a valid Excel date.
- #VALUE! error occurs if either:
- The supplied start date is not a valid Excel date, or
- The months argument is not supplied as a numerical value.
- If months is not an integer value, the function adds the integer portion only to start_date and ignores the decimal fraction.
- Microsoft Excel stores dates in sequential serial numbers where the serial number 1 represents 1/1/1900 and displays valid Excel date because of default date format. For more info, visit Microsoft Website.