The Excel EDATE Function to Add Months
EDATE is a Date function. It returns the date a specified number of months after a given date.
The EDATE Function returns the date serial number that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. But EDATE is not sophisticated enough to avoid weekends (for example).
Syntax:
EDATE(start_date, months)
Arguments:
Start_date (Required). The start date. You can enter dates using the DATE function, or they can occur as results of other formulas or functions. For example, use DATE(2022,8,23) for 23 August 2022. Problems can occur with dates entered as text.
Months (Required). The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.
Remarks:
Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. 1 January 1900 is serial number 1. Thus, 1 January 2000 is serial number 36526 because it is 36,525 days after 1 January 1900.
If start_date is not a valid date, EDATE returns the #VALUE! error value.
If months is not an integer, it is truncated.
Example
Column C documents the data and formulas in column A.
In cell A2, type 2022-01-13 (or any other date you like) and press Enter. The date should appear in the default format for your computer. The column width should adjust to show all the data.
We use yyyy-mm-dd format for entry because it is unambiguous. In the USA, 01/02/2022 means January 2, 2022, whereas the rest of the world sees it as 1 February.
In cell A3, enter =EDATE(A2,1). The result will be exactly one month after the date in A2.
In cell A4, enter =EDATE(A2,-2) [note the “minus two”!]. You should see a date exactly two months before the date in A2.
Note: I often confuse EDATE (add months to a date) with EOMONTH (end of month, this month or some months hence). EOMONTH is also very useful.