Written by Allen Wyatt (last updated September 23, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 and back out of, 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 manifested, use Save As to save the workbook in native Excel format. (Well, in the Excel format native to Excel 2007 through later versions of Excel.) Since compatibility mode is not involved from that point on, the problem should not occur again. If the problem is manifested, 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/18, both of the following will return the same result:
=LastOfMonth(B7) =LastOfMonth("10/15/2018")
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6613) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
When working with text in a formula, you may need to extract the left-most characters from a string of text or from a ...
Discover MoreWhen you need to count a number of cells based upon a single criteria, the standard function to use is COUNTIF. This tip ...
Discover MoreThe LET function provides an easy way to define and use variables within a formula. This tip shows you how you can start ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-09-23 10:49:28
Dave Bonin
Personally, I've always liked the formula
= DATE( YEAR( A1 ), MONTH( A1 ) + 1, 0 )
It's elegantly simple as the zeroth day of a month is the last day of the prior month.
It also doesn't depend on my end users having the Analysis ToolPak enabled. I never understood why Excel didn't make all of its functions always available as the ToolPak isn't that big.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments