EOMONTH Function is Flakey

Joe uses the EOMONTH function quite frequently, but since he moved up to Excel 2007 he has had problems. Joe has the Analysis ToolPak active and the EOMONTH function works, at first. However, when I save my workbook and open it later, the cells containing the EOMONTH function look fine until you click on them. Then the formula bar shows #N/A. The formula has disappeared. Analysis ToolPak is still active when this happens. This does not happen on every occasion, but it is too frequent to ignore. Also, Joe has a colleague who is experiencing the same problem with his workbooks.

This problem with EOMONTH apparently occurs because of changes made in "native functions" in Excel 2007. In previous versions of Excel, EOMONTH was part of the Analysis ToolPak. In Excel 2007 the function has been moved out of the ToolPak and into Excel. In other words, you don't need to have the Analysis ToolPak activated in order to use EOMONTH.

This leads to the problem. If you have a workbook created in a previous version of Excel and you open it in Excel 2007's compatibility mode, the workbook formulas are evaluated and, under some circumstances, the internal "tokens" used for functions are updated. When the workbook is saved back out, the updated token is stored in the workbook and, when the workbook is reloaded, the token now points to what Excel interprets as an invalid function.

Microsoft hasn't posted anything in their Knowledge Base about this error as of yet. The problem seems to be intermittent (as Joe noted), affecting a workbook only after it has gone through between four and eight open/edit/save cycles.

The solution is to open the workbook and, if the problem is not manifest, use Save As to save the workbook in native Excel 2007 format. Since compatibility mode is not involved from that point on, the problem should not occur again. If the problem is manifest, then you will need to correct the problem (as Joe has done in the past) and then save the workbook in Excel 2007 format.

If it is not possible for you to save the workbook in Excel 2007 format (perhaps you need to use the older format in order to work with others who have not updated their program), then you should consider not relying on the EOMONTH function. Instead, use a formula such as either of the following:

=A1+31-DAY(A1+31)
=DATE(YEAR(A1),MONTH(A1)+1,1)-1

If you prefer, you can create your own user-defined function to calculate the last day of a month. The following is one approach:

Function LastOfMonth(Any_Date As Date) As Date
    ' Returns the date of the last day of
    ' the month of the passed date argument

    LastOfMonth = DateAdd("d", -1, _
      DateAdd("m", 1, Month(Any_Date) _
      & "/1/" & Year(Any_Date)))
End Function

You'll want to make sure that you pass the function a valid date, either by referencing a date in a cell or by enclosing a literal date within quote marks. Assuming cell B7 contains the date 5/10/09, both of the following will return the same result:

=LastOfMonth(B7)
=LastOfMonth("5/10/2009")