Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EOMONTH Formula
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EOMONTH Formula
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 he 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 "KRiffe" wrote in message ... | 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EOMONTH Formula
<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 "Niek Otten" wrote in message ... | 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 he | | 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 | | | | "KRiffe" wrote in message ... || 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? | | |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EOMONTH Formula
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 "Niek Otten" wrote in message ... | 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 he | | 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 | | | | "KRiffe" wrote in message ... || 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? | | |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EOMONTH Formula
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 "KRiffe" wrote in message ... | 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 | | "Niek Otten" wrote in message ... | | 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 he | | | | 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 | | | | | | | | "KRiffe" wrote in message ... | || 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? | | | | | | | |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EOMONTH Formula
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. "Niek Otten" wrote: 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 "KRiffe" wrote in message ... | 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 | | "Niek Otten" wrote in message ... | | 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 he | | | | 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 | | | | | | | | "KRiffe" wrote in message ... | || 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? | | | | | | | |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EOMONTH Formula
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. -- Biff Microsoft Excel MVP "KRiffe" wrote in message ... 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. "Niek Otten" wrote: 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 "KRiffe" wrote in message ... | 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 | | "Niek Otten" wrote in message ... | | 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 he | | | | 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 | | | | | | | | "KRiffe" wrote in message ... | || 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? | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
eomonth | Excel Worksheet Functions | |||
EOMONTH() question | Excel Worksheet Functions | |||
conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) | |||
conditional formatting: problem entering EOMONTH formula... | Excel Discussion (Misc queries) |