How to Use the EDATE Function in Excel

This guide will explain how to use the EDATE function in Excel to return a date a specific number of months before or after a given date.

The EDATE function is one of Excel’s built-in date functions that enables you to add or subtract a specified number of months to a date with ease.

It is often used in financial analysis to calculate maturity dates for investments that last several months. Users who are setting up reports may also benefit from the EDATE function to create dynamic date ranges given a starting date.

In this guide, we will provide a step-by-step tutorial on how to start using the EDATE function in Excel.

The Anatomy of the EDATE Function

The syntax of the EDATE function in Excel is as follows:

EDATE(start_date, months)

Let’s look at each argument to understand how to use the EDATE function.

• = the equal sign is how we start any function in Google Sheets.
• EDATE() refers to our EDATE function. This function accepts a start date and a positive or negative integer and returns a serial number representing a date a certain number of months before or after the start date.
• start_date refers to a user-specified starting date. Dates must be entered using the DATE function to ensure that we’re entering a valid and accurate date.
• The months parameter allows us to specify the number of months to add or subtract from the starting date. A positive value returns a future date while a negative value yields a past date.
• If the months argument contains a decimal value, the value is truncated and read as an integer.
• If the output of the EDATE appears as an integer, the user must apply date formatting or use a function such as TEXT to convert the serial number into a readable date.

A Real Example of Using the EDATE Function in Excel.

Let’s explore a simple example where we can use the EDATE.

In the example above, we have a table containing a treasury bond with the following details:

• The bond was issued on January 1st, 2024.
• The bond has a maturity date of 18 months after the issue date.

Given these details, we want to calculate the actual maturity date of our bond.

We can use the following formula to find the date exactly 18 months after January 1st, 2024:

=EDATE(B1,B2)

The formula above aims to add or subtract a specific number of months to a starting date. Since our number in cell B2 is positive (18), the EDATE function will calculate a future date by adding 18 months to our starting date in cell B1.

After evaluating the EDATE formula, we’ve determined that our bond has a maturity date of July 1st, 2025.

Let’s try using the EDATE function to find a date a specific number of months in the past.

Suppose you want to find the date exactly 3 months before the current date. We can calculate this using the following formula:

=EDATE(TODAY(), -3)

Our months argument is a negative number to specify that we want to subtract months from the starting date.

After evaluating the function, we’ve now determined the exact date three months before the current date.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to start using the EDATE function in Excel.

How to Use the EDATE Function in Excel

1. Select an empty cell where you wish to output the result of the EDATE function.
   
   In this example, we’ll use EDATE to return the maturity date of a bond that has a maturity date 18 months after its starting date of January 1st, 2024.
   
2. In your desired cell, type “=EDATE(“ to start the EDATE function.
   
3. While your cursor is still in the formula bar, type the starting date to use.
   
   In our example, we’ll type the cell reference B1 which holds the value of our starting date.
   
4. Next, type a comma and input the number of months as the second argument.
   
5. Hit the Enter key to evaluate the EDATE function.
   
   In the example above, EDATE returned a serial number that represents the date 18 months after the provided starting date.
   To convert the serial number to a readable date, select the cell with the serial number and change the number formatting to “Short Date” in the Home tab.
   The output of EDATE should now be displayed as a readable date.

FAQs

1. Why does my EDATE function return a #VALUE! error?
   The #VALUE! error may occur if the start date provided to the EDATE function is not a valid date format recognized by Excel. Make sure your start date is in a date format (e.g., MM/DD/YYYY or DD/MM/YYYY, depending on your regional settings). You can also encounter this error if the months argument is non-numeric.
   
2. Can I use the EDATE function to subtract months from a date?
   Yes, you can use the EDATE function to subtract months from a date by providing a negative number as the second argument.
3. How does the EDATE function handle end-of-month dates when adding or subtracting months?
   If the resulting month does not have the same day number (e.g., adding one month to January 31st), Excel adjusts the result to the last day of the resulting month.
   For example, EDATE(“2024-03-31”, 1) would result in February 28, 2024 (or February 29 in a leap year), as February does not have a 31st day.

To learn more about using Excel for performing operations on dates, you can read our post on how to filter dates by year in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!