Excel EDATE Function – How To Use

The Excel EDATE function is categorized as a DATE and TIME function. It’s a handy function for when you want to calculate a date and number of months in the future or the past. You can use this function to compute the maturity of investments, accounts receivable, and due dates for any other purpose.

Excel EDATE Function

Syntax

The syntax of the EDATE function is as follows:

=EDATE(start_date, months)

Arguments:

'start_date' – The date from which you want to start your computation. The date needs to be valid, i.e., Excel must recognize it as a date. If the date is entered as text, the function will return a #VALUE error.

'months' – This is where you input the number of months you want to move forward or backward from the start_date. Enter a positive value for getting a future date and a negative value for getting a date in the past.

Important Characteristics of the EDATE Function

  • Entering an invalid start_date will cause the function to return a #VALUE error.
  • The function will ignore the time component if any, is attached to the date supplied in the start_date
  • The argument entered in the months argument must be an integer. If you enter a decimal value, the function will ignore it.
  • The function’s output cell must be formatted as a date, otherwise, the date will be displayed as a serial number.

Examples of EDATE Function

Let’s see some examples to understand the EDATE function in detail.

Example 1 – Plain Vanilla Formula for the EDATE Function

Let’s start with a bare minimum of what EDATE function can do for us. This is a basic EDATE formula that will take us to a certain number of months into the future or in the past, depending on what we enter in the months argument.

=EDATE(A2, 2)

Excel-EDATE-Function-Example-01

In the example, we have added two months to Dec 25, 2001, in the first row and subtracted 3 months in the second row. Naturally, the formula returns the same day — 2 months later and 3 months before, respectively.

Example 2 – Using EDATE function with TODAY function

If our start_date happens to be the same day as the date of entry, you can nest the TODAY function into the EDATE function and drag it along the cells as you add new entries. To do this, we’ll use the following formula:

=EDATE(TODAY(), 4)

Excel-EDATE-Function-Example-02

As on the day of writing, the date is Jun 12, 2021. I’ve added 4 months in the second argument. The formula, therefore, computes the same day 4 months into the future and returns Oct 12, 2021.

Example 3 – Using EDATE function to move “n” year(s) into the future or past

While we won’t use any specific function to accomplish this, we’ll just sprinkle some elementary logic into the formula. We’ll multiply the number of years we want to move forward or backward by 12. This converts it into months, and we’ll have fulfilled the formula’s requirements.

Excel-EDATE-Function-Example-03

This is the formula we’ll use in our example:

=EDATE(A2, 3*12)

Note that we could have also just directly written 36 instead of 3 X 12. 36 months into the future from the initial date Dec 25, 2001, takes us to Dec 25, 2004. Easy-peasy, isn’t it?

Example 4 – Using EDATE function with COUNTIFS function

We’re a fast-paced startup and we have 99 pending tasks, but we won’t let deadlines be one. How must we do this? Like always, we’ll use some Excel formulas.

Let’s say we have a list of tasks we need to fix within the next 12 months. Solving them is mission-critical because we’ll have to clean our slate before our next round of funding.

Excel-EDATE-Function-Example-04

We’ll use Excel to keep a score of these problems and our target dates, using this formula:

=COUNTIFS(($B$2:$B$13),">=" & D2,($B$2:$B$13),"<" & EDATE(D2,1))

When you’ve set this formula up in a spreadsheet, you can even add new targets for solving problems as they come up. Note that the months in column D are actually the first date of each month (for example Jan 1, Feb 1, Mar 1, etc.), and then formatted as "mmm-yyyy" to display just the name of the month along with the year. Setting the date to the first of each month is essential for the formula to work. So, let’s look at what’s happening in this formula.

The COUNTIFS function counts the number of cells in a range supplied by us, provided it also matches the criteria supplied by us. In our example, we have added two pairs of range and criteria.

  • Range and Criteria 1

The range we’ve supplied in the first argument contains the target dates. We want to compare them with the date in cell D2 to see if the target date falls after the date in D2.

Think about it, we want to count a cell only if the target date falls within that month. For instance, when counting the targets for January, we want to count only those cells that have a target date between Jan 1 and Jan 31.

Therefore, we will compare the range to see if any of those dates fall after the date in cell D2 by adding ">=" in the formula. If there is, the formula will move on to see if the second range and criteria pair can be fulfilled as well. Otherwise, the formula will simply return 0.

  • Range and Criteria 2

The range, naturally, remains the same here as well. This time around, we want to compare the range with the last date of the month we’re checking for. Therefore, we’ll select the first day of the month and use the EDATE function to add exactly one month to it.

We will ask Excel to check if any of the dates that made past the first range and criteria pair falls before the output date given by the EDATE function. We do this by adding a "<" in the formula.

When any date makes it through both range and criteria pairs, the COUNTIFS function will add 1 to the final count.

Example 5 – Compute Duration to Retirement

We take care of our employees. During their last two years with us before retirement, we like to enroll our employees in a 4-week free post-retirement planning session, to make the transition easier for them. But we have many employees. So, we decided to have our receptionist maintain a spreadsheet for us.

The spreadsheet contains all employee’s names with their birthdays. We let Excel formulas take care of the remaining part. We accomplished this with the following formula:

=IF(YEARFRAC(TODAY(),EDATE(B2,12*60)) <= 2, "Eligible", "Not Eligible")

Excel-EDATE-Function-Example-05

Since we have a list of birthdays from our employees, we’ll start by computing their day of retirement. We do this by using our plain vanilla EDATE function to add 60 years (or 720 months) to each employee’s birth date.

Once we have the date of retirement for each employee, we’ll use the YEARFRAC function to compute the difference between the retirement date and today’s date. Note that the output of the YEARFRAC function is to be read differently than we normally would.

For instance, 5.5 years would normally mean 5 years, 6 months. However, if the YEARFRAC function’s output is 5.5, it is to be read as 5 years, 5 months. So, any employee with a number 2 or less is eligible to be enrolled for the post-retirement planning session and to display that we have used an Excel IF function.

That’s all our EDATE ninja techniques. EDATE is a simple, handy function that can make your spreadsheets much easier to work with. Practice these formulas on your own spreadsheets, and before you’re done, we’ll be back with another powerful Excel function.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.