how to use excel edate functionGet the same date of future and past months.

EDATE Function will return a date which is the number of months after or before from a date which you specify.

For example, if you specify date 01-Apr-2016 & 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 Function while calculating maturity dates and for defining deadlines for tasks.

Syntax

EDATE(start_date,months)

start_date A date from which you want to start.

months Number of months after & before you want to calculate.

More Information on Excel EDATE Function

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

Examples

Examples For Excel EDATE Function

  1. In the first example, I have used 5 as numbers of months & it has added exactly 5 months in 1-Jan-2016 & returned 01-June-2016. January & June has 5 months difference between them.
  2. In the second example, I have used -1 months & it has given 31-Dec-2016, a date which is exactly 1 month back from 31-jan-2016.
  3. In the third example, I have inserted a date directly into the function.

EDATE function 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. You just have to mention the date and number of months.

Samples File

download sample file to learn about excel edate function

What’s Next?

To learn more about Excel EDATE Function you can check Microsoft’s Help Section.

And, if you have a unique idea to use the edate function, I would love to hear from you.

There are also you have EOMONTH function which is highly useful like edate function. Apart from this, I have a list of excel functions and some real life formulas examples.