The EDATE function in Excel is used to calculate a date that is a certain number of months before or after another date. The syntax of the EDATE function is:
=EDATE(start_date, months)
where start_date is the initial date and months is the number of months to add or subtract. The start_date can be entered as a date value, a cell reference, or a text string in a valid date format. The months argument can be positive or negative, and can also be a cell reference or a formula.
The EDATE function returns a date value that can be formatted as a date in Excel. The EDATE function is useful for calculating dates such as maturity dates, expiration dates, due dates, etc.
Example: Using the EDATE function to calculate the maturity date of a bond
Suppose you have a bond that was issued on January 1, 2024 and has a maturity period of 5 years. You want to use the EDATE function to calculate the maturity date of the bond. Here are the steps to do so:
- Enter the issue date of the bond in cell A1, for example
1/1/2024. - Enter the maturity period of the bond in cell B1, for example
5. - In cell C1, enter the formula
=EDATE(A1, B1*12). This will calculate the date that is 5 times 12 months, or 60 months, after the issue date. - Format cell C1 as a date, for example
mm/dd/yyyy. The result will be1/1/2029, which is the maturity date of the bond.
The following table shows the input and output of the EDATE function in this example:
| Issue date | Maturity period (years) | Maturity date |
|---|---|---|
| 1/1/2024 | 5 | 1/1/2029 |
Other approaches to calculate dates in Excel
Besides the EDATE function, there are other ways to calculate dates in Excel, such as:
- The DATE function, which takes the year, month, and day as arguments and returns a date value. For example,
=DATE(2029, 1, 1)returns the same date as=EDATE(1/1/2024, 5*12). - The DATEVALUE function, which converts a text string in a valid date format to a date value. For example,
=DATEVALUE("1/1/2029")returns the same date as=EDATE(1/1/2024, 5*12). - The YEAR, MONTH, and DAY functions, which extract the year, month, and day from a date value, respectively. For example,
=YEAR(EDATE(1/1/2024, 5*12))returns2029, which is the year of the maturity date. - The YEARFRAC function, which calculates the fraction of a year between two dates. For example,
=YEARFRAC(1/1/2024, 1/1/2029)returns5, which is the number of years between the issue date and the maturity date.
