How to use EDATE Function in Excel

The EDATE function returns the date which is the number of months after or before from the date which you specify.

For example, if you specify date 01-Apr-2016 and 5 as a month number, it will return 1-Sep-2016 in the result as September is exactly 5 months after April.

You can use EDATE while calculating maturity dates and for defining deadlines for tasks.

Syntax

EDATE(start_date,months)

  1. start_date A date from which you want to start.
  2. months Number of months after & before you want to calculate.

Examples

Below we have used EDATE with different types of arguments.

using edate function with different arguments
  1. In the first example, we have used 5 as numbers of months and it has added exactly 5 months in 1-Jan-2016 and returned 01-June-2016.
  2. In the second example, we have used -1 months and it has given 31-Dec-2016, a date which is exactly 1 month back from 31-jan-2016.
  3. In the third example, we have inserted a date directly into the function.

Remember

  1. Excel store dates as serial numbers, so start date should be a valid date.
  2. And, if the start date is not a valid date, it will return #VALUE! error.
  3. Use a positive number in “months” to get a future date and a negative number for a past date.

Note: EDATE is quite smart to give you the same date in future and past. It doesn’t matter that there are 31 days, 30 days or 28 days in a month.

Sample File