how to use excel edate function

Quick Intro

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 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 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

Here I have used EDATE with different types of arguments.

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.

Sample File

download sample file to learn more about this tips

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 it, I would love to hear from you.

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



  • Ogundepo Ezekiel Adebayo

    Thank you. I’ve not heard of emonth before in excel.