Microsoft KB Archive/96791

= Microsoft Knowledge Base =

Excel: EDATE Returns #VALUE! Error Value or All Results Same
Last reviewed: September 12, 1996

Article ID: Q96791

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh version 4.0

SUMMARY
The EDATE function (an Analysis ToolPak add-in function) may not function correctly if:

 It is used in a formula that has been entered as an array. -or- The value for the Start_Date argument is a defined range.

Workaround
To use the EDATE function when the Start_Date argument is a defined range, use the VALUE function as in the following example

=EDATE(VALUE(DateRange),2) where DateRange is the defined name for the Start_Date argument.

MORE INFORMATION
The EDATE function returns the serial number date that is the indicated number of months before or after the Start_Date argument. Because of the way the EDATE function processes information, it may not work correctly when the function is used in a formula that has been entered as an array or if the Start_Date argument is a defined range.

EDATE in Array Formula
The following formulas will not work correctly when entered into the indicated cells as arrays:

Cell Range  Formula -- --

D1:D7: =EDATE(DateRange,2) (where DateRange is defined from C1:C7) B1:B7: =EDATE(A1:A7,2) If you are using Microsoft Excel for Windows, each cell in the range will display a #VALUE! error value. If you are using Microsoft Excel for the Macintosh, each cell will contain the same value as the first in the range (in the above example, if B1 displays the date 1/1/93, so will B2 through B7).

Start_Date Argument a Defined Range
If the start_date argument is a defined range and you do not use the VALUE function to convert the dates in the range to serial number dates, you will receive a #VALUE! error value or all of your results will be identical. For example, the following function, with the defined range &quot;DateRange&quot;

=EDATE(DateRange,2) will not work correctly.