The EDATE formula with example in Excel

The EDATE function in Excel is used to add or subtract a specified number of months to a given date. This is particularly useful for calculating due dates, expiration dates, or any other future or past dates based on a starting date.

Syntax

excel
EDATE(start_date, months)

• start_date: The initial date from which to calculate the new date.
• months: The number of months to add to (positive number) or subtract from (negative number) the start_date.

Example

Let's consider some examples to understand how the EDATE function works.

Example 1: Adding Months to a Date

Suppose you have the date January 15, 2024, in cell A1.

• A1: 2024-01-15 (January 15, 2024)

To calculate the date three months after January 15, 2024:

1. In cell B1, enter the formula:

   excel
   =EDATE(A1, 3)
   

   This will output 2024-04-15 (April 15, 2024).

Example 2: Subtracting Months from a Date

Using the same initial date in cell A1 (2024-01-15), to calculate the date three months before January 15, 2024:

1. In cell B2, enter the formula:

   excel
   =EDATE(A1, -3)
   

   This will output 2023-10-15 (October 15, 2023).

Example 3: Adding Months to a Date in Text Format

Let's say you have the date in text format in cell A2:

• A2: 15/01/2024 (January 15, 2024 in DD/MM/YYYY format)

To calculate the date six months from January 15, 2024:

1. In cell B3, enter the formula:

   excel
   =EDATE(A2, 6)
   

   This will output 2024-07-15 (July 15, 2024).

Example 4: Practical Use Cases

1. Loan Repayment Date: Calculate the next loan repayment date.
2. Subscription Renewal Date: Determine when a subscription needs to be renewed.
3. Project Milestone Date: Establish project milestones based on monthly intervals.

Assume you have the start date of a subscription in cell A3:

• A3: 2024-03-01 (March 1, 2024)

To calculate the renewal date 12 months later:

1. In cell B4, enter the formula:

   excel
   =EDATE(A3, 12)
   

   This will output 2025-03-01 (March 1, 2025).

Notes

• Date Format: Ensure the start_date is recognized as a date by Excel. If the result appears as a serial number, format the cell as a date to display it correctly.
• Leap Years: The function handles leap years appropriately. For example, adding one month to January 31, 2024, will result in February 29, 2024, since 2024 is a leap year.
• Text Dates: When using text dates, ensure they are in a recognizable format or use the DATE function to ensure accuracy.

Summary

The EDATE function is a powerful tool for managing and calculating dates in Excel. Whether adding or subtracting months from a given date, it simplifies the process of date manipulation for various practical applications.