ExcelFunctions.net

Excel EDATE Function

Related Function:
Home » Excel-Built-In-Functions » Excel-Date-And-Time-Functions » Excel-Edate-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 syntax of function is:

EDATE( start_date, months )

Where the arguments are as follows:

start_date - The initial date, from which to count the number of months.
months - The number of months to add to (or subtract from) the start_date.

Note that Microsoft advises that you do not type dates directly into functions, because Excel interprets text representations of dates differently, depending on the date interpretation settings on your computer. Therefore the start_date argument for the Edate function should be input as either:

  • A reference to a cell containing a date
    or
  • A date returned from another function or formula

Warning:   Although you can input date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.


Edate Function Examples

The following spreadsheets show examples of the Edate function. The formulas are shown in the spreadsheet on the left, and the results are shown in the spreadsheet on the right.

 Formulas:
  A B
1 31-Jan-2008 =EDATE( A1, 9 )
2 31-Jan-2008 =EDATE( A2, 22 )
3 31-Jan-2008 =EDATE( A3, -16 )
4 28-Feb-2008 =EDATE( A4, 12 )
5 29-Feb-2008 =EDATE( A5, 12 )
 Results:
  A B
1 31-Jan-2008 31-Oct-2008
2 31-Jan-2008 30-Nov-2009
3 31-Jan-2008 30-Sep-2006
4 28-Feb-2008 28-Feb-2009
5 29-Feb-2008 28-Feb-2009

The above examples illustrate that:

  • You can use negative values for the months argument, to get a date that is before the supplied start_date.
  • The function is able 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.

Note also that, as recommended by Microsoft, in all calls to the Edate function, the start_date has been supplied as a cell reference.


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


Edate Function Errors

If you get an error from the Excel Edate function, this is likely to be one of the following:

Common Errors
#VALUE! - Occurs if either:
- the supplied start_date is not a valid date
or
- the supplied months argument is non-numeric
#NAME? -

Occurs if the Analysis ToolPak add-in is not enabled in your Excel.
You will need to enable this if you want to use the Excel Edate function.

To do this 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

To do this 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

To do this in Excel 2010 :

  • Click the File tab (top left of your spreadsheet) and select Options
  • From the menu on the left hand side, select Add-Ins
  • If the window that pops up doesn't show the 'Add-ins' list, use the 'Manage:' drop-down menu (at the bottom of the window) to select Excel Add-ins. Click Go...
  • From the 'Add-Ins' window, select the option Analysis ToolPak and click OK