How to use the EDATE function in Excel

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:

1. Enter the issue date of the bond in cell A1, for example 1/1/2024.
2. Enter the maturity period of the bond in cell B1, for example 5.
3. 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.
4. Format cell C1 as a date, for example mm/dd/yyyy. The result will be 1/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:

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)) returns 2029, 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) returns 5, which is the number of years between the issue date and the maturity date.