Article ID: 179545
Article Last Modified on 8/25/2006
APPLIES TO
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 98 for Macintosh
This article was previously published under Q179545
SYMPTOMS
If you use the EOMONTH function to return the last day of a
month, the function may return the first day of the next month instead.
For example, the EOMONTH function may incorrectly return 3/1/2100
(March 1, 2100) instead of 2/28/2100 (February 28, 2100).
CAUSE
This problem occurs when the date returned by the EOMONTH
function meets the following conditions:
- The date is in February. -and-
- The date 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 year 1900
or 2000.
WORKAROUND
Because the EOMONTH function should always return a date at
the end of the month, you can modify the formulas so that they subtract one
from the date returned by the EOMONTH function if the date is not at the end of
the month. This forces the function to return the correct date, for example:
A1: 1/1/2100
A2: =EOMONTH(A1,1)
The formula in cell A2 returns 3/1/2100, an incorrect date.
However, if you change the formula
A1: 1/1/2100
A2: =IF(DAY(EOMONTH(A1,1))=1,EOMONTH(A1,1)-1,EOMONTH(A1,1))
the formula returns the correct date (2/28/2100) even though the
date falls in February of a century year that is not evenly divisible by 400.
This is how the formula works:
If the day of the end of the month is 1, recalculate the end of the
month, subtract one, and return the date. Otherwise, recalculate the
end of the month and return the date.
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.
Additional query words: XL97 y2k year2000 XL
Keywords: kbbug kbpending KB179545