EOMONTH Formula

[KRiffe]

Built a worksheet in Excel 2003 which uses EOMONTH formula. The EOMONTH
formula required the Analysis Toolpak add-in to work. That worksheet is
stored on a network server and used by several individuals. Recently moved
to Excel 2007 and all users accessing that worksheet have activated the
Analysis Toolpak. When working in the file, had to rebuild the EOMONTH;
saved the file, went back in it the next day and that formula has now turned
to #N/A in the cell yet the date is being displayed as if the formula is
working.

Has anyone had any issues with this formula in 2007? Assuming the EOMONTH
formula does not work - any suggestions for an alternative formula?

 

[Niek Otten]

In Excel2007 the former Analysis Toolpak functions are built-in functions. So there is no need anymore to load that Toolpak (that
is, not for using the Functions). If you happen to use a non-English version of Excel, there are a few issues, described here:

http://www.rondebruin.nl/atp.htm

A good alternative for the EOMONTH(A1,0) function is:

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

Adjust the 0 in the MONTH part to your needs

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| Built a worksheet in Excel 2003 which uses EOMONTH formula. The EOMONTH
| formula required the Analysis Toolpak add-in to work. That worksheet is
| stored on a network server and used by several individuals. Recently moved
| to Excel 2007 and all users accessing that worksheet have activated the
| Analysis Toolpak. When working in the file, had to rebuild the EOMONTH;
| saved the file, went back in it the next day and that formula has now turned
| to #N/A in the cell yet the date is being displayed as if the formula is
| working.
|
| Has anyone had any issues with this formula in 2007? Assuming the EOMONTH
| formula does not work - any suggestions for an alternative formula?

 

[Niek Otten]

<Adjust the 0 in the MONTH part to your needs>

That should have read:

Adjust the +1 part.....etc

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| In Excel2007 the former Analysis Toolpak functions are built-in functions. So there is no need anymore to load that Toolpak
(that
| is, not for using the Functions). If you happen to use a non-English version of Excel, there are a few issues, described here:
|
| http://www.rondebruin.nl/atp.htm
|
| A good alternative for the EOMONTH(A1,0) function is:
|
| =DATE(YEAR(A1),MONTH(A1)+1,0)
|
| Adjust the 0 in the MONTH part to your needs
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
|
|| Built a worksheet in Excel 2003 which uses EOMONTH formula. The EOMONTH
|| formula required the Analysis Toolpak add-in to work. That worksheet is
|| stored on a network server and used by several individuals. Recently moved
|| to Excel 2007 and all users accessing that worksheet have activated the
|| Analysis Toolpak. When working in the file, had to rebuild the EOMONTH;
|| saved the file, went back in it the next day and that formula has now turned
|| to #N/A in the cell yet the date is being displayed as if the formula is
|| working.
||
|| Has anyone had any issues with this formula in 2007? Assuming the EOMONTH
|| formula does not work - any suggestions for an alternative formula?
|
|

 

[KRiffe]

Any reason why the EOMONTH formula (which appears not to be) wouldn't still
work in 2007?

 

[Niek Otten]

It will still work. Any chance you ran into the problems described in Ron's site?

BTW what exactly do you mean by "does not work"?
Error message? What message?
Wrong result? What were the inputs, what result did you expect and what did you get instead?


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Any reason why the EOMONTH formula (which appears not to be) wouldn't still
| work in 2007?
|
| "Niek Otten" wrote:
|
| > <Adjust the 0 in the MONTH part to your needs>
| >
| > That should have read:
| >
| > Adjust the +1 part.....etc
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | In Excel2007 the former Analysis Toolpak functions are built-in functions. So there is no need anymore to load that Toolpak
| > (that
| > | is, not for using the Functions). If you happen to use a non-English version of Excel, there are a few issues, described
here:
| > |
| > | http://www.rondebruin.nl/atp.htm
| > |
| > | A good alternative for the EOMONTH(A1,0) function is:
| > |
| > | =DATE(YEAR(A1),MONTH(A1)+1,0)
| > |
| > | Adjust the 0 in the MONTH part to your needs
| > |
| > | --
| > | Kind regards,
| > |
| > | Niek Otten
| > | Microsoft MVP - Excel
| > |
| > |
| > |
| > || Built a worksheet in Excel 2003 which uses EOMONTH formula. The EOMONTH
| > || formula required the Analysis Toolpak add-in to work. That worksheet is
| > || stored on a network server and used by several individuals. Recently moved
| > || to Excel 2007 and all users accessing that worksheet have activated the
| > || Analysis Toolpak. When working in the file, had to rebuild the EOMONTH;
| > || saved the file, went back in it the next day and that formula has now turned
| > || to #N/A in the cell yet the date is being displayed as if the formula is
| > || working.
| > ||
| > || Has anyone had any issues with this formula in 2007? Assuming the EOMONTH
| > || formula does not work - any suggestions for an alternative formula?
| > |
| > |
| >
| >
| >

 

[KRiffe]

I am using a English version of Excel 2007 but I am saving the workbook as
Excel 97-2003 so our current Excel 2003 users can continue to use the
spreadsheet. I've built a formula as such:

In B2, I've built the following formula =EOMONTH(A2,1) where A2 I've input
1/31/08. In cells C2, D2, E2, etc., I have copied that formula over so that
the A2 reference is moving with each cell so that I can arrive at Jan - Dec
2008 month end dates across the page.

When I first build the formula, it works as expected. When I return to the
worksheet - either after other users have accessed or I've copied the
worksheet (which isn't linked to anything else) to another location (say a
jump drive) and then open the worksheet, I receive a error message - File
Error: Data may be lost.

When that happens, Excel will turn those previously working formulas, along
with other formulas I have in my worksheet that are dependent on the right
date being stored in the cell (e.g., NetworkingDays, VLOOKUP, HLOOKUP, and
OFFSET) formulas to #N/A. I believe those formulas are breaking and bein
converted to #N/A as they are dependent on a good value in A2, B2, C2, etc.
The spreadsheet continues to display the dates as if they are stored in the
respective cells, but when I click on cell A2, B2, C2, etc., the value that
is actually stored there is #N/A.

I've had to rebuild these formulas so many times since moving to Excel 2007
that I'm just about to stop using the EOMONTH formula - which is unfortunate
as I believe that is a very powerful formula and one I have relied on through
the years. I'm going to try your alternative formula suggestion as really
can't keep redoing work as some of my formulas are rather complicated and to
have them continually break is not productive.

 

[T. Valko]

I have been unable to duplicate your problem.

I opened Excel 2007 put in some EOMONTH formulas and saved as *.xls format.
Closed Excel 2007
Opened the file in Excel 2002 (ATP loaded) and the formulas were OK.
Closed Excel 2002
Opened the file in Excel 2007 (Compatability mode) and the formulas were OK.

If it's any consolation, the work-around for EOMONTH is fairly simple.