XL: EOMONTH 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 above, 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
  

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)

   A1: 1/1/2100
   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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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)

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

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