The Excel EDATE Function

Home » Excel-Built-In-Functions » Excel-Date-And-Time-Functions » Excel-Edate-Function

Search this site:

Custom Search

Related Function :

EOMONTH Function

Basic Description

The Excel EDATE function returns a date that is a specified number of months before or after a supplied start date.

The format of the function is :

EDATE( Start_date, Months )

where the arguments are as follows :

Start_date	-	The initial date
Months	-	The number of months to add to (or subtract from) the Start_date

Example

The following spreadsheet shows examples of the Edate function.

The spreadsheet on the left shows the function formats and the spreadsheet on the right shows the results.

Formulas

	A	B
1	31/01/2008	=EDATE( A1, 9 )
2	31/01/2008	=EDATE( A2, 22 )
3	31/01/2008	=EDATE( A3, -16 )
4	28/02/2008	=EDATE( A4, 12 )
5	29/02/2008	=EDATE( A5, 12 )

Results

	A	B
1	31/01/2008	31/10/2008
2	31/01/2008	30/11/2009
3	31/01/2008	30/09/2006
4	28/02/2008	28/02/2009
5	29/02/2008	28/02/2009

Note how you can use negative values for the Months argument, to get a date that is before the supplied Start_date.

Also note how the function manages to cope with months that do not contain the same day number as the Start_date. For example, there is no 31st day of November, so the function returns the 30th. Similarly, the function can handle the leap year day - returning 28/02/2009 as the date 12 months after 29/02/2008.

Further examples of the Excel Edate function can be found on the Microsoft Office website.

Common Error

If you get the #NAME? error from your Excel Edate function this is likely to be because the Analysis ToolPak add-in is not enabled. To include the Edate function, enable the Analysis ToolPak add-in through the following steps :

In Excel 2003 :

From the Tools drop-down menu, select the option Add-Ins ...
An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK

In Excel 2007 :

Click the Microsoft button on the top left of your spreadsheet and select the Excel Options button
From the menu on the left hand side, select Add-Ins
In the 'Manage:' box, select Excel Add-ins and click Go...
An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK