Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EDATE Function disappears intermittently
Hi all,
Hope somebody knows the answer to this one. I have a new computer set up, which has Excel 2003 installed (11.8237.8221) SP3. The Analysis Tool Pack has been added into the installation. The entire office uses 2003, there are no 2007 implementations to confuse things, although the compatability packs have been installed on individual computers. I have a user that accesses a 2003 spreadsheet, which uses the EDATE format. Intermittently (eg once every few days/once a week), the user notes that instead of the EDATE function working correctly, the #NAME thing is displayed instead of the date. (the cell where this function is used is a paste-link, and no direct data entry is done in this area). I check the list of functions, and EDATE is no longer in the list!!!! I check the Tool Add-ins, and the Analysis Toolpack is still selected!!!! The only thing I have been able to do to get it to restore is to close Excel, reopen it again, remove the Analysis toolpack and OK it, then re-add the Analysis toolpack again. I don't know what other functions the Analysis toolpack adds to the list to see if other functions have also disappeared from the Excel function list, but the EDATE is definitely missing. It's like Excel 'drops' or 'forgets' the pack is installed??? This is the only user this happens for, we have other spreadsheets and users using the EDATE format and the issue does not happen for them, only for this one user, and occassionally. Any help with this issue would be very much appreciated, I have no idea how to fix it on the long term? TIA, Wendy -- Wendy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
EDATE Function disappears intermittently
I don't have any suggestions on how to fix the problem with the ATP.
You can use formulas that don't rely on ATP functions. This one is more user friendly but longer: A1 = some date B1 = number of months variable =DATE(YEAR(A1),MONTH(A1)+B1,MIN(DAY(A1),DAY(DATE(Y EAR(A1),MONTH(A1)+B1+1,0)))) This one is more compact but is not real user friendly: =MIN(DATE(YEAR(A1),MONTH(A1)+/-{n,n+/-1},DAY(A1)*{1,0})) Where +/- depends on whether the month variable is a positive or negative number and where n = month variable. Since the formula uses an array constant the month variable has to be hardcoded into the formula. You could use a range of cells but that would make it an array formula. If the month variable is a positive number then: =MIN(DATE(YEAR(A1),MONTH(A1)+{n,n+1},DAY(A1)*{1,0} )) For example: A1 = some date Month variable = 2 =MIN(DATE(YEAR(A1),MONTH(A1)+{2,3},DAY(A1)*{1,0})) If the month variable is a negative number then: =MIN(DATE(YEAR(A1),MONTH(A1)-{n,n-1},DAY(A1)*{1,0})) For example: A1 = some date Month variable = -2 =MIN(DATE(YEAR(A1),MONTH(A1)-{2,1},DAY(A1)*{1,0})) -- Biff Microsoft Excel MVP "Wendy" wrote in message ... Hi all, Hope somebody knows the answer to this one. I have a new computer set up, which has Excel 2003 installed (11.8237.8221) SP3. The Analysis Tool Pack has been added into the installation. The entire office uses 2003, there are no 2007 implementations to confuse things, although the compatability packs have been installed on individual computers. I have a user that accesses a 2003 spreadsheet, which uses the EDATE format. Intermittently (eg once every few days/once a week), the user notes that instead of the EDATE function working correctly, the #NAME thing is displayed instead of the date. (the cell where this function is used is a paste-link, and no direct data entry is done in this area). I check the list of functions, and EDATE is no longer in the list!!!! I check the Tool Add-ins, and the Analysis Toolpack is still selected!!!! The only thing I have been able to do to get it to restore is to close Excel, reopen it again, remove the Analysis toolpack and OK it, then re-add the Analysis toolpack again. I don't know what other functions the Analysis toolpack adds to the list to see if other functions have also disappeared from the Excel function list, but the EDATE is definitely missing. It's like Excel 'drops' or 'forgets' the pack is installed??? This is the only user this happens for, we have other spreadsheets and users using the EDATE format and the issue does not happen for them, only for this one user, and occassionally. Any help with this issue would be very much appreciated, I have no idea how to fix it on the long term? TIA, Wendy -- Wendy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EDATE Function | Excel Worksheet Functions | |||
Horizontal-Scroll Navigation Bar Intermittently Disappears | Excel Discussion (Misc queries) | |||
Edate function Name error | Excel Discussion (Misc queries) | |||
edate function | Excel Worksheet Functions | |||
edate function | Excel Worksheet Functions |