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
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.
|
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.
Date before the start date
To indicate a date before the start_date, use a negative number for the months argument.
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.