EOMONTH Function is Flakey

by Allen Wyatt
(last updated November 1, 2016)

4

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 he saves his workbook and opens 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 the EOMONTH function in later versions of Excel (Excel 2007 and later). In earlier versions of Excel (Excel 2003 and before) EOMONTH was part of the Analysis ToolPak. In the later versions of Excel the ToolPak is no longer required; the function is part of Excel itself. 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 an earlier version of Excel and you open it using the compatibility mode in a later version of Excel, 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 format. (Well, in the Excel format native to Excel 2007 through Excel 2013.) 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 and rebuild the workbook.

If it is not possible for you to save the workbook in Excel's current native format (perhaps you need to use the older Excel 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 10/15/13, both of the following will return the same result:

=LastOfMonth(B7)
=LastOfMonth("10/15/2013")

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6613) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Specifying a Location To Save Automatic Backup Files

When Word creates automatic backups of your documents, you may not like where Word stores them. This naturally leads to the ...

Discover More

Offering Options in a Macro

It is often helpful to get user input within a macro. Here's a quick way to present some options and get the user's response.

Discover More

Understanding ASCII and ANSI Characters

Two of the most common character coding schemes used in computers go by the acronyms ASCII and ANSI. This tip explains a bit ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Nesting IF Worksheet Functions

The IF worksheet function is very handy to make conditional evaluations. You are not limited to a single IF comparison, ...

Discover More

Returning the Left-most Characters

When working with text in a formula, you may need to extract the left-most characters from a string of text or from a cell. ...

Discover More

Returning the MODE of a Range

The MODE function is used to determine the most frequently recurring value in a range. This tip explains how to use the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

2016-11-02 02:34:15

Andries

I had the same problem with conditional formatting when saving older formats in compatibility mode. Fortunately the problem was resolved by saving in current native format or plain excel workbook.


2016-11-01 11:13:27

Gary

Actually Daniel (and Allen), there is a slight problem with the =A1+31-DAY(A1+31) formula.

If your A1 date is right at the end of a month (something like 31st Aug) then adding the 31 takes it past September and into October, so results in the last date of the following month instead of the current one.


2016-11-01 09:21:27

Daniel Marek

Hi Franz,
Allen's formula with adding 31 days works fine for whatever date (being it in Feb or not), just try it.

Cheers
Daniel


2016-11-01 07:39:22

Franz Verga

Hi Allen,

this works also and is more compact:

=DATE(YEAR(A1),MONTH(A1)+1,0)

while

=A1+31-DAY(A1+31)

is not good when you are switching from a month with 31 days to a month with 30, or even with February.

Regards
Franz


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.