The Excel EDATE function is a Date & Time formula that calculates and returns the serial date number for a certain number of months before or after a given date. EDATE function is useful for calculating maturity dates or due dates that fall on the same day of the month as the date of issue. In this guide, we’re going to show you how to use the Excel EDATE function and also go over some tips and error handling methods.


Supported versions

  • All Excel versions

Excel EDATE Syntax

EDATE(start_date, months)


Arguments

start_date The start date. Microsoft recommends using DATE or other similar functions that return a date as a serial number.
months

Number of months before or after start_date.

  • Use a positive integer for future dates
  • Use a negative integer for past dates

Examples

Date after the start date

All arguments of the EDATE function are required. Start with start_date by entering a date value from a formula. You can use the DATE function to generate a date. Enter a positive number for the months argument to point to a future date in the same day of the month.

=EDATE(DATE(2018,1,29),5)

Date before the start date

To indicate a date before the start_date, use a negative number for the months argument.

=EDATE(DATE(2018,1,29),-1)

Download Workbook


Tips

  • EDATE function is useful for calculating expiration dates, maturity dates, due dates, and other similar functions.
  • Excel truncates the months. For example, both 1 and 1.7 will yield the same results.
  • Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.

Issues

  • If start_date is not a valid date, EDATE function returns the #VALUE! error value. Microsoft recommends using a formula to generate a valid date serial value.