XL: EDATE Function May Be Incorrect If Date Is in February

• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, if you use the EDATE function to return a date that is a specific number of months before or after another date, the function may return an incorrect result.

For example, the EDATE function may incorrectly return 3/1/2100 (March 1, 2100) instead of 2/28/2100 (February 28, 2100).

CAUSE

This problem occurs when the following conditions are true:

• The date that is returned by the function should be February 28th.

  -and-

• The start date that is used by the function falls on the 29th, 30th, or 31st of the month (for example, July 30th).

  -and-

• The date that is returned by the function is in a century year that is not evenly divisible by 400, for example:

        2100, 2200, 2300, 2500, 2600, 2700, 2900, 3000
  

WORKAROUND

To work around this problem, manually modify the formulas that use the EDATE function so that they return the correct result.

Example

Assume that you are using the following date and formula:

   A1: 1/31/2100
   A2: =EDATE(A1,1)

   A1: 1/31/2100
   A2: =EDATE(A1,1)-1

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The EDATE function, which is included in the Analysis ToolPak, allows you to return a date that is a specific number of months before or after another date. For example, if you want to know the date six months from today, you can use the following formula:

   =EDATE(TODAY(),6)

The problem described in this article occurs only when the EDATE function returns a date in February of a century year that is not a leap year, for example:

   A1: 1/27/2100   B1: =EDATE(A1,1)
   A2: 1/28/2100   B2: =EDATE(A2,1)
   A3: 1/29/2100   B3: =EDATE(A3,1)
   A4: 1/30/2100   B4: =EDATE(A4,1)
   A5: 1/31/2100   B5: =EDATE(A5,1)

The formulas in cells B3, B4, and B5 return the incorrect result, 3/1/2100, instead of the correct result, 2/28/2100. The problem occurs because the start dates fall on the 29th, 30th, and 31st of the month.

For more information about leap years, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q118923
   TITLE     : XL: Method to Determine Whether a Year Is a Leap Year