Select the product you need help with
EOMONTH Function May Be Incorrect If Date Is in FebruaryArticle ID: 179545 - View products that this article applies to. 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:
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 The formula in cell A2 returns 3/1/2100, an incorrect date.
However, if you change the formula A2: =EOMONTH(A1,1)
A1: 1/1/2100 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.
A2: =IF(DAY(EOMONTH(A1,1))=1,EOMONTH(A1,1)-1,EOMONTH(A1,1)) 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. MORE INFORMATION The EOMONTH function, which is included in the Analysis
ToolPak, allows you to return the date of the last day in a month. For example,
if you want to determine the last day of the month six months from today's
date, use the following formula:
=EOMONTH(TODAY(),6)
If today is 1/13/98, the function returns 7/31/98: the last day
of the month six months from today. The problem described in this article occurs only when the EOMONTH function returns a date in February of a century year that is not a leap year, for example:
A1: 1/1/2000 B1: =EOMONTH(A1,1) The formulas in cells B1 and B5 return the correct results
(2/29/2000 and 2/29/2400) because the dates are in century years that are
evenly divisible by 400. A2: 1/1/2100 B2: =EOMONTH(A2,1) A3: 1/1/2200 B3: =EOMONTH(A3,1) A4: 1/1/2300 B4: =EOMONTH(A4,1) A5: 1/1/2400 B5: =EOMONTH(A5,1) The formulas in cells B2, B3, and B4 return incorrect results (3/1/2100, 3/1/2200, 3/1/2300) because the dates are in century years that are not evenly divisible by 400. PropertiesArticle ID: 179545 - Last Review: August 25, 2006 - Revision: 2.0
|
Back to the top
