A to Z of Excel Functions: The EDATE Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the EDATE function.

The EDATE function

This function returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). EDATE should be used to calculate maturity dates or due dates that fall on the same day of the month as the date of issue / first date. This can be useful for infrastructure analysis too.

The EDATE function employs the following syntax to operate:

The EDATE function has the following arguments:

• start_date: this is required and represents the start date.  Dates should be entered by using the DATE function, or as results of other formulas or functions.  For example, consider using DATE(2020,5,23) for the 23rd day of May, 2020.  Problems can occur if dates are entered as text
• months: this is also required.  This represents the number of months before or after the start_date.  A positive value for months yields a future date; a negative value yields a past date.

It should be further noted that:

• Excel stores dates as sequential serial numbers so that they can be used in calculations.  January 1, 1900 is serial number 1, and July 6, 2009 is serial number 40000 because it is 39,999 days after January 1, 1900
• if start_date is not a valid date, EDATE returns the #VALUE! error value
• if months is not an integer, it is truncated
• you will need to change the number format (CTRL + 1, ‘Format Cells’) in order to display a proper date
• February 29, 1900 is recognised as day 60 on the 1900 date system.  This date does not exist (years ending in “00” must be divisible by 400 to be a leap year), but this error has been perpetuated to be consistent / compatible with Lotus 1-2-3.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.