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

ID: Q179583

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SYMPTOMS

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 30).

-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

NOTE: This problem does not occur if the date is in 1900 or 2000.

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)

The formula in cell A2 returns 3/1/2100; however, the date you expect to receive is 2/28/2100. You can correct the formula by adding "-1" (without the quotation marks) to the end of the formula, as in the following example:


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

The formula returns 2/28/2100, which is the correct result.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.

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)

If today is 1/13/98, the function returns 7/13/98, the day that is six months after today.

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 B1 and B2 return the correct results (dates 2/27/2100 and 2/28/2100), because the start date does not fall on the 29th, 30th, or 31st of the month.

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:

Q118923 XL: Method to Determine Whether a Year Is a Leap Year

When you enter or fill dates in a worksheet in Microsoft Excel 97, a date that should appear as January 1 may instead appear as February 1. For more information on about this issue, please see the following article in the Microsoft Knowledge Base:

Q175362 XL97: January 1 May Appear as February 1 in a Date

Additional query words: XL97 y2k year2000

Keywords : kbtool kb2000 xlformula xladdins
Version : :; MACINTOSH:98
Platform : MACINTOSH
Issue type : kbbug